Help with LIKE search and Many to One relationship... :( GURUs???

[GregL83]

Hello,

 

I'll create a case study for this to make sense...

 

I have two tables T1 and T2.

 

T1 has a list of books.  T2 has pictures associated with the books in T1.  More than one picture can exist for a book in T1.  So, the books have a 1 to many relationship with the pictures.

 

I created a search to return books from T1... And it works fine.  I then wanted to return a picture with each book from T2.  The problem is that T2 may have more than one image associate with a book in T1.  This results in the book being returned as a result more than once...  I have been search for logic to only return the latest (i have timestamps) picture from T2 for each book in T1 so that I don't return duplicate results of books.

 

So far my query is as follows:

[GregL83]

I needed to create another post cause the wiz editor was acting up...

 

query:

$query = "SELECT b.id, b.name, b.slug, p.pic FROM books b, book_pics p WHERE (name LIKE '%".$phrase."%' OR slug LIKE '%".$phrase."%') AND p.book_id = b.id"

 

Thanks in advance for any help... Your time and efforts are greatly appreciated

[ignace]

SELECT DISTINCT b.name, p.uri

FROM books b, pictures p

WHERE p.book_id = b.id

ORDER BY p.timestamp DESC

[GregL83]

I tried that.  I also tried to select distinct b.id ...  My thoughts are that mysql is not selecting a distinct id or name, but row.  There rows are not distinct because the picture field is different each time.  I thought your answer would have worked, but I'm still returning the same book more than once (same amount of times as images)...

[ignace]

My thoughts are that mysql is not selecting a distinct id or name, but row.

 

You are correct DISTINCT doesn't work on fields it's not a FUNCTION

 

My code should work as it:

 

1. Selects all matching records from pictures with books (one book, multiple images)

2. Orders these records according to the pictures date in reverse order (latest first) Still one book, many images

3. Applies DISTINCT for each book one image

 

Post your SQL code and an image of the result

[GregL83]

    $query = "SELECT DISTINCT s.id, s.name, s.slug, s.overall_comments, uc.comment FROM scenes s, user_comments uc WHERE (name LIKE '%".$phrase."%' OR slug LIKE '%".$phrase."%') AND uc.scene_id = s.id ORDER BY uc.update_time DESC";
    $result = mysql_query($query);

 

here is the two arrays outputted... they are the same record, but there are 2 associated comments....

 

Array ( [0] => 1 [id] => 1 [1] => the pour house bar & grill [name] => the pour house bar & grill [2] => the-pour-house-bar-grill-boston-massachusetts [slug] => the-pour-house-bar-grill-boston-massachusetts [3] => [overall_comments] => [4] => blah! [comment] => blah! ) Array ( [0] => 1 [id] => 1 [1] => the pour house bar & grill [name] => the pour house bar & grill [2] => the-pour-house-bar-grill-boston-massachusetts [slug] => the-pour-house-bar-grill-boston-massachusetts [3] => [overall_comments] => [4] => [comment] => )

[ignace]

It makes no sense to include the comment remove it. This is also why you get the same record twice your result is DISTINCT

[GregL83]

what if i need the comment for each result??? i realize i could loop through and do another query... but i thought there might be a way to do it in one query... does anyone know how to do this????

[GregL83]

Just an update... I've been searching long and hard to find a solution to this problem.  I fear there is a simple solution i've overlooked.  The closest i've gotten I can limit the results to 1 record per 'scene' by using GROUP BY.  I can also retrieve the max update time using MAX(update_time); however, the comment isn't retrieved from the row with the max update time...

 

$query = "SELECT s.id, s.name, s.slug, s.overall_comments, uc.comment, MAX(uc.update_time) FROM scenes s, user_comments uc WHERE (name LIKE '%".$phrase."%' OR slug LIKE '%".$phrase."%') AND uc.scene_id = s.id GROUP BY s.id";

 

Anyone???