Need help with complex query

Transact-SQL
1

Because it's a little beyond my skillset, I'm struggling to write a clean, compact query to accomplish the following:

I have three tables 1. webexc 2. webeximg 3. newsauthor

Each record in webexc is a news item with, among other things, an itemID (primary key), and authorID. webeximg contains images that correspond to each news item, and has an imgID (primary key) and itemID. newsauthor has authorID and authorName

I'm trying to compose a query that pulls from all three tables that results in:

the news item (itemID, authorID, headline, etc)
the images which correspond to that item ID
and the name of the author of the news item

I've tried multiple joins but I don;t think I have the proper syntax. Any help would be GREATLY appreciated.

2

Your data structure sounds like you only have one, or perhaps zero/one, Image & Author per News Story, but if you were to allow multiple Images per news story (i.e. a 2-part Foreign Key of primary key or itemID & imgID) it would cloud the issue a bit as each row returned would not be unique to a news story, but the News and Author data would be repeated on multiple rows - one for each image in that new story.

Either way, this will work for zero/one/many rows in Image /Author tables for a single News recrd.

SELECT N.itemID,
       N.authorID,
       ... other columns from webexc ...
       A.authorName,
       ... other columns from newsauthor ...
       I.imgID,
       ... other columns from webeximg ...
FROM webexc AS N
     LEFT OUTER JOIN newsauthor AS A
        ON A.authorID = N.authorID 
     LEFT OUTER JOIN webeximg AS I
        ON I.itemID  = N.itemID
3

Thanks for the quick reply. I'll give this a try now. You CAN have more than one image per news item. I was thinking maybe GROUP BY webexc.headline? I'll try this and then cross that bridge. Thanks again.

4

Generally speaking I would use two result sets - provided that the "outer query" is only selecting a single news item:

SELECT N.itemID,
       N.authorID,
       ... other columns from webexc ...
       A.authorName,
       ... other columns from newsauthor ...
FROM webexc AS N
     LEFT OUTER JOIN newsauthor AS A
        ON A.authorID = N.authorID 
WHERE N.itemID = @YourNewsID
--
SELECT N.itemID,
       I.imgID,
       ... other columns from webeximg ...
FROM webexc AS N
     JOIN webeximg AS I
        ON I.itemID  = N.itemID
WHERE N.itemID = @YourNewsID

and then the application can display a "record card" for the first recordset, with the "News Item", and then use the database connectivity interface (ADO / ODBC whatever) to do a "Next Record Set" and then display the data from that, multi-row resultset, in a Grid, or similar, with all the Images

If you have multiple news stories in the output it is harder. You can output ALL the data on EVERY row, and have the APP just ignore the duplicate columns where there are multiple rows for a single news item, but its wasteful of bandwidth and likely to adversely effect performance.

The other way is to do two resultsets, one for News Items and the second for Images, both sorted into the same order, and have the APP traverse both simultaneously reading the next News Story, checking the 2nd resultset to see if it matches that News ItemID and if so display however many data rows of images there are, if not then not displaying any images - a subsequent News ItemID will then match the Image Resulset (because they are sorted in the same order), but tis quite complex to set up in the APP (unless you are using something that "just does all that stuff for you" :smile: