CODEWITHSUNDEEP
sqlsql-servert-sqljoin
JimmyK
JimmyK

Reputation: 4992

Combining the results of two JOINS in SQL

Tickets last 24 hours and can be either 'Child', 'Adult' or 'Teen'. Essentially, a ticket can be 'ConnectedTo' a number of movies. Child tickets for example will be connected to movies with a age rating of PG or below, while Adult tickets will be connected to every single movie.

The below returns info on all the tickets purchased for movies from the previous month:

SELECT *
FROM 
[Test_DB].[dbo].[Tickets]
INNER JOIN 
[Test_DB].[dbo].[Movies]
ON 
[Test_DB].[dbo].[Tickets].[ConnectedTo] = [Test_DB].[dbo].[Movies].[MovieID]
WHERE 
[Test_DB].[dbo].[Tickets].[DateEntered] >= DATEADD(month, DATEDIFF(month, 0, GETDATE())-1, 0) AND [Test_DB].[dbo].[Tickets].[DateEntered] <= DATEADD(month, DATEDIFF(month, 0, GETDATE()), -1)

Each ticket can have any number of notes attached to it. For example, if a special discount was given to a ticket holder, a note will be created about that. Similiarly, if a ticket is exchanged for another or refunded, a note will be created.

The below will return all the notes for a given Ticket based on the TicketID:

SELECT *
FROM 
[Test_DB].[dbo].[Tickets]
JOIN [Test_DB].[dbo].[Notes]
ON [Test_DB].[dbo].[Tickets].[TicketID] = [Test_DB].[dbo].[Notes].[ConnectedTo]
WHERE TicketId = 64903

My question is, is there a way to combine the two? For example, the first bit of SQL could return 5 tickets, each with a different TicketID. Based on that, I want to make use of my second SQL to return all of the notes for those 5 tickets.

I was thinking of using UNION to combine the two selects... Though my brain keeps telling me that I need to JOIN the two pieces of SQL on the TicketId's, however every time I try to it comes out wrong. Any help would greatly be appreciated! Thanks.

Upvotes: 0

Views: 97

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

I imagine that "notes" could be optional. If so, then you will want a left join rather than an inner join. Also, the use of aliases and eliminating the unnecessary square brackets would make your query easier to read:

SELECT *
FROM Test_DB.dbo.Tickets t INNER JOIN 
     Test_DB.dbo.Movies m
     ON t.ConnectedTo = m.MovieID LEFT JOIN
     Test_DB.dbo.Notes n
     ON t.TicketID = n.ConnectedTo
WHERE t.DateEntered >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) -1, 0) AND
      t.DateEntered <= DATEADD(month, DATEDIFF(month, 0, GETDATE()), -1);

Note (no pun intended): if a ticket has multiple notes, then you will get multiple rows in the output.

Upvotes: 1

Esteban Elverdin
Esteban Elverdin

Reputation: 3582

This should work

SELECT [Test_DB].[dbo].[Tickets].*, [Test_DB].[dbo].[Notes].*
FROM [Test_DB].[dbo].[Tickets]
INNER JOIN [Test_DB].[dbo].[Movies]
ON 
[Test_DB].[dbo].[Tickets].[ConnectedTo] = [Test_DB].[dbo].[Movies].[MovieID]
INNER JOIN [Test_DB].[dbo].[Notes]
ON [Test_DB].[dbo].[Tickets].[TicketID] = [Test_DB].[dbo].[Notes].[ConnectedTo]
WHERE [Test_DB].[dbo].[Tickets].[DateEntered] >= DATEADD(month, DATEDIFF(month, 0, GETDATE())-1, 0) 
  AND [Test_DB].[dbo].[Tickets].[DateEntered] <= DATEADD(month, DATEDIFF(month, 0, GETDATE()), -1)

Upvotes: 1

Related Questions