How to write efficient sql query to select from multiple tables?

Question

Ok, I have three tables. The parent table is Listings and two child tables Images and Tags.

Here is database schema

Listings Table

ListingID (PK)      Title                     Images          Tags
1                   Fruits                    1               1
2                   Furniture                 0               0
3                   Electronics               0               0

Images Table

ImageID        ListingID (FK)              ImageName
1              1                           fruits.png
2              1                           fruits_2.png
3              1                           fruits_3.png

Tags

TagID          ListingID (FK)               Tag
1              1                            apple
2              1                            banana
3              1                            melon

So I need to select from listings by listingid, and if it has images join images table and if it has tags table.

As output I would like to recieve

For Example I select Listing ID = 1

Title            Images                                    Tags
---------------------------------------------------------------------------
Fruits           fruits.png|fruits_2.png|fruits_3.png      apple|banana|melon

Keep in mind the Listing table has 100K hits per day. So we need to eliminate row locking

If you have better idea how select from those 3 tables please share with me example.

Answer 1

You can also do

SELECT l.Title, i.ImageName, t.Tag FROM
    Listings l
    LEFT OUTER JOIN Images i ON l.ListingID = i.ListingID
    LEFT OUTER JOIN Tags t ON l.ListingID = t.ListingID
WHERE l.ListingID = 1

You will have to massage the produces resultset, but in my work I prefer to separate the pure SQL from application logic.

Answer 2

try

select p.Title, 
stuff((select ', ' + i.ImageName from Images i where i.ListingID = p.ListingID for xml path('')),1,2,'') as Images,
stuff((select ', ' + t.Tag from Tags t where t.ListingID = p.ListingID for xml path('')),1,2,'') as Tags
from Listing p

For further reference on the possible options to achieve your goal see http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

BUT I would recommend using 3 simple SELECTs and building the needed strings client-side...