Simple SQL query question

[hc1619]

Hi there.. I have a dating site.. and on the member profile page I need to query two tables to get the info.. so i do it like so:

SELECT members.*, MainProfile.*
From Members
INNER JOIN MainProfile
ON Members.MemberID = MainProfile.MemberID
Where Members.MemberID = @memberID

But I'm going to add the functionality to upload multiple photos to a profile.. up to 5 photos.. and I'm going to display the 5 thumbnails on the profile page.

I'm going to store the photo filenames in a separate table.
So for each photo a member uploads a new row will be created.

What i'm wondering is - how should I best query this? Can I get this info by doing another join on the query above? (and if so, what type of join, as that query is a fixed row one, whereas this will be potentially up to 5 rows from another table). OR is it better to have a separate SQL query all together to get the photo filenames.

Which would be faster? thanks!

 

[Krickles]

Try something like:

SELECT Members.*, MainProfile.*, PhotoTable.*
FROM Members m
INNER JOIN MainProfile mp ON m.MemberID = mp.MemberID
LEFT OUTER JOIN PhotoTable pt ON m.MemberID = pt.MemberID

I'm suggesting a LEFT OUTER JOIN so members and the assocaited profile will load with or without an entry in the photo table.

BTW, it's best to avoid * in SELECT statements.

Hope this helps,

Krickles | 1.6180

 

[hc1619]

thanks a lot for that, thats exactly what i was looking for.

im just thinking about it.. the stored proc that has this code is my most heavily used proc.. thousands of times a day.. I'm wondering how much slower this is going to make it all and how much load i will be introducing? FOr instance - (and this could sound like a dumb idea), could I not just store a list of the photo filnames in a field in my MainProfile table?