selecting from 3 tables.....with a twist ...HELP

[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 CAR can 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 for 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

 

[jimbopalmer]

if I understand what you want, a max(pic_id) or similar aggregate will insure that you get at most one pic per car per user.

Select user_name, user_blah_blah, car_name, car_blah_blah, max(pic_id from tblUsers, tblCars, tblPics where [do your left outer joins here] group by user_name, user_blah_blah, car_name, car_blah_blah I tried to remain child-like, all I acheived was childish.

 

[imstillatwork]

I'll try that, but i think i want a max(carID) from tblpics, right?

tblCars does not have pic_ids, many pics per car. tblPics has car_ids. just wanna make sure, i have never used max() heck havent used most of the cool features of SQL...

 

[jimbopalmer]

Yes you want it from tblpics.

we are just using an aggregate like max() to force a single row to be returned, if you SQL allows it you could say tblpics.rownum = 1 but I bet the max() works on more SQLs I tried to remain child-like, all I acheived was childish.

 

[imstillatwork]

thanks!