SQL Problem

Factual Questions
1

I’ll se if I can explain this properly. I’m not that good at explanations.

I have a database table with titles and studios as two columns. It’s a list of cartoons and the studios they were developed at. The studios column (an integer value) is linked to a studios table. So we can get that with a join:

JOIN iad_titles on iad_studios.id = iad_titles.studio

But we also have a link table for associated studios with the fields titleNo and studioNo.

Now … ALL the cartoons are going to have a main studio listing. They may not have a listing in the associated studios But when I search for cartoons by the studio, I want to find all the toons that connect to that studio as well as all the ones that might have an associated studio listed.

Any ideas?

2

I would think all you need to do is to slap on a LEFT OUTER JOIN link_table on link_table.title = iad_titles.id and link_table.studio = ?

3

This is exactly what outer joins are for. I don’t know the precise schema of your tables, but something like this should do the trick



SELECT title FROM cartoons c 
JOIN studios s ON c.studio_id = s.studio_id
OUTER JOIN associated_studios as ON as.studio_id = s.studio_id AND as.cartoon_id = c.cartoon_id
4

friedo’s on the right track, but the join isn’t quite right (plus you’ll run into issues trying to use the alias “as” - that’s a reserved system word).

I think something like this is what you’re going for:


SELECT 
    Cartoons.title, Studios.StudioName, ascStud.StudioName
FROM 
    Cartoons
    INNER JOIN Studios ON Cartoons.studioNo = Studios.studioNo
    LEFT OUTER JOIN 
        ( AssociatedStudios 
          INNER JOIN Studios AS ascStud ON AssociatedStudios.studioNo = ascStud.studioNo
        ) ON AssociatedStudios.titleNo = Cartoons.titleNo
WHERE
    Studios.StudioName = @studioName OR ascStud.StudioName = @studioName
5

:smack:

6

I think that’s close, but what I’m trying to get is the Studio Name and the ReleaseYear of all the titles associated with that studio. I can do it fine with just the studio > titles link:


SELECT iad_studios.ID, iad_studios.studio, ISNULL(iad_titles.releaseYear, 0) as releaseYear
         FROM (iad_studios LEFT OUTER JOIN iad_titles ON iad_studios.ID = iad_titles.studio) 
         WHERE (LEFT(iad_studios.studio, 1)  = @param1)
         ORDER BY iad_studios.studio, releaseYear

(@param1 is a letter of the alphabet selected by a DropDown box)

but how do I adjust that to pick up titles that have an associated studio as well?

7

In what way is my query not giving you the results you need (other than the extra columns)? Given a studio name, it will return all titles where that studio is either the primary studio or an associated studio. Is that what you want?

8

Well, I tried to make it a bit easier, but what I really want is, given a list of studios, give me back all the studios with a specific fist letter (chosen by a DropDown Box) and the earliest ReleaseDate of any titles associated with that studio.

But I’ll accept a list of studios, titles and release dates.

9

If I’m understanding you correctly, you could get the list of studio names and earliest release date with this query:


SELECT name, MIN(release_date) from (
	SELECT s.name, t.release_date FROM studios s
	INNER JOIN titles t on s.id = t.studio.id
	WHERE LEFT(s.name, 1) = @param1
) group by name;

(note, because it’s an inner join, it’ll ignore any studios that don’t have titles.)

However, if you want the accompanying title that was released on that date, it’s a little trickier. Hopefully your titles table has a unique id?


SELECT a.name AS studio_name, a.earliest_release_date, b.name as earliest_released_title FROM (
	SELECT name, id, MIN(release_date) AS earliest_release_date from (
		SELECT s.name, t.release_date, t.id FROM studios s
		INNER JOIN titles t on s.id = t.studio.id
		WHERE LEFT(s.name, 1) = @param1
	) group by name
) AS a 
INNER JOIN titles b on a.id = b.id;
10

Still not sure I’m following exactly, but if I’m getting it, then I think you actually want a UNION, not a left join.


SELECT * FROM
( SELECT 
      Studios.StudioName, Cartoons.title
  FROM 
      Studios
      INNER JOIN Cartoons ON Cartoons.studioNo = Studios.studioNo
  UNION
  SELECT 
      Studios.StudioName, Cartoons.title
  FROM 
      Studios
      INNER JOIN AssociatedStudios ON AssociatedStudios.studioNo = Studios.studioNo
      INNER JOIN Cartoons ON AssociatedStudios.titleNo = Cartoons.titleNo
) AS t
WHERE
	StudioName LIKE @param1 + '%'

This gives the list of all studios along with all titles associated to that studio. Am I getting closer?

(Do Not Taunt, you are missing the associated studios in your query. There’s a linking table for other studios besides the main one. I think.)

11

You’re right, I totally missed that. Is the idea that a title has a main studio, but also zero or more associated studios? If so, I agree you’d have to add in the union.

12

That worked! Here’s what I ended up with:


SELECT * FROM
( SELECT 
      iad_studios.studio, iad_titles.title, iad_titles.releaseYear, iad_studios.id
  FROM 
      iad_studios
      INNER JOIN iad_titles ON iad_titles.studio = iad_studios.id
      WHERE (LEFT(iad_studios.studio, 1)  = @param1)
  UNION
  SELECT 
      iad_studios.studio, iad_titles.title, iad_titles.releaseYear, iad_studios.id
  FROM 
      iad_studios
      INNER JOIN iad_studios_link ON iad_studios_link.studioNo = iad_studios.id
      INNER JOIN iad_titles ON iad_studios_link.titleNo = iad_titles.id
  WHERE (LEFT(iad_studios.studio, 1)  = @param1)
  ) AS t
       ORDER BY studio, releaseYear

No, I can do it programmaticaly, but the next trick is to only return one entry per studio with the smallest releaseYear. But I’m on babysitting duty now, so that’s tomorrow’s project.

Thanks, everyone!

13

Do Not Taunt’s second query is the way to do that - just replace his inner subquery with the Union subquery.