SQL Join three tables

Question

I'm learning advanced SQL queries little by little and I'm fairly stumped with a problem:

I have three tables: news, author, and images. Each field in the news table (newsID) is a news story, which then has an associated author in the author table (authorID) and can have any number of images associated in the images table. Each image has and associated (newsID). So each story has one author but can have several images.

I want to make a list of all news stories and use just one of the images as a thumbnail image. The problem is that any sql query I try to list the news items with gives me results equal to the number of images in the images table rather than the number of news items.

I don't know where to go from here. Any help would be greatly appreciated.

Answer 1

If the 3 tables in question are [news], [author] and [image] with appropriate columns, then

Derived Table approach

you can have a derived image table to get one image per news and then join it with the news and author table as shown. This has been written and tested in SQL Server.

SELECT  
      N.[newsStoryTitle]
        ,A.authorName
        ,I.imageData1
  FROM [news] N
  LEFT OUTER JOIN author A ON A.newsID = N.newsID
  LEFT OUTER JOIN 
    (
    SELECT newsID, MAX(imageData) AS imageData1 FROM [image] 
    GROUP BY newsID
    )  AS I ON I.newsID = N.newsID
ORDER BY N.newsID

You could replace the LEFT OUTER JOINs with INNER JOINs if you do not need news without any images.

Correlated Subquery approach (as suggested by Marcelo Cantos)

If the imageData is stored as a text or image, then the MAX in the derived table wouldn't work. In that case, you can use a correlated subquery like this:

SELECT  N.newsStoryTitle ,
        A.authorName ,
        I.imageData
FROM    dbo.news N
        INNER JOIN dbo.author A ON N.newsID = A.newsID
        INNER JOIN dbo.image I ON N.newsID = I.newsID
WHERE   imageID = ( SELECT  MAX(imageID)
                    FROM    dbo.image
                    WHERE   newsID = N.newsID
                  )
ORDER BY n.newsID

Answer 2

One option is to add the following predicate:

FROM news JOIN images ...
...
WHERE imageID = (SELECT MAX(imageID)
                   FROM image
                  WHERE newsID = news.newsID)

Note that this excludes news items without an image. If you don't want this, you'll need a left join on images and an additional condition on the WHERE:

FROM news LEFT JOIN images ...
...
WHERE imageID IS NULL
   OR imageID = (SELECT MAX(imageID)
                   FROM image
                  WHERE newsID = news.newsID)