LEFT OUTER JOIN . . . With a twist...

[imstillatwork]

Hello,
I have a query I can't figure out.
3 tables:

1)tblUsers
2)tblCars
3)tblPics

One USERS can have many CARS, on CARcan have many PICS.
I want to make a search.

As is right now, I select the USER and CAR info that is needed. The results are looped over, displaying each car.

During each loop, I run another query that looks up ONE PIC from the CAR that will be displayed.

so this means if there are 10 cars returned per page (there are at least that) the getPics Query runs 10 times to show the page. talk about inefficient.

HOW CAN I combine all three tables into one query so that..

all the USER information is selected, all the CAR information is selected, and ONE pic of each car is selected....if I do an left outer, then i get repeated results because I am displaying in CARS, and there can be many pics each car. it is OK if this happens with USERS, i want the user repeated for every car that he has.

MAKE ANY SENSE?

thanks
Kevin

 

[r937]

i don't think you can do it in one query

see 3.5.4 The Rows Holding the Group-wise Maximum of a Certain Field

http://www.mysql.com/doc/en/example-Maximum-column-group-row.html

rudy

http://rudy.ca/

 

[imstillatwork]

they MySQL guys gave me some things to try using MAX()
I have yet to try it, but it sounds promising. The problem is, I just don't know what is avavilable to use for more advanced queries.

thanks