CODEWITHSUNDEEP
sql-serverjoin
Wilson L
Wilson L

Reputation: 17

SQL Server : Join from multiple table references

Forgive me for adding yet another JOIN question, but I've been stumped all day and haven't been able to find a good answer for this.

I'm trying to join 4 tables, such that they look like below:

QuarterID    ReviewID    SaleID    PotentialID
    1           1           1          1
    1           2           2         null
    1           3         null         2
    1           4         null        null

The relevant info from the tables is below

Sale:

  QuarterID
  ReviewID
  IsArchived

Potential:

  QuarterID
  ReviewID
  IsArchived

Quarter:

  ID

Review:

  ID

We can have multiple Sales and Potentials associated with one Quarter-Review pairing, but only one Sale and one Potential will have IsArchived = 0 for the given Quarter-Review pairing.

SELECT 
    quarter.id    AS QID, 
    review.id     AS RID, 
    Sales.id      AS SID, 
    Potentials.id AS PID 
FROM
    dbo.quarter 
JOIN 
    (SELECT * 
     FROM dbo.sale 
     WHERE isarchived = 0) AS Sales ON Sales.quarterid = quarter.id 
JOIN 
    (SELECT * 
     FROM dbo.potential 
     WHERE isarchived = 0) AS Potentials ON Potentials.quarterid = quarter.id 
JOIN 
    dbo.review ON dbo.review.id = Sales.reviewid   
               AND dbo.review.id = Potentials.reviewid 
ORDER BY 
    quarter.id, rid

Using the above (there are some unnecessary columns, I know), I've managed to get the joins so that they get the 1st condition (where its all the Sales and Potentials that are in the same Quarter and Review combination, but I also want to see if there is a Quarter/Review combo with only a Sale and no Potential, if there is a Q/R combo with only a Potential and no Sale, and just every Quarter and Review combo, since there are only a few Q/R combos that have both a Sale and Potential, with almost all of the Q/R combos only having a Sale or Potential.

I guess overall the difficulty comes from needing to get the join from two intermediate tables. I can join Quarter, Sale, and Review easily, but having the Potential table joining on the same fields (ReviewID, QuarterID) as Sale is making me only get the AND, and I can't figure out an OR. I've been throwing around ORs for hours trying to get the right sequence without any luck. Help?

--Edit to include sample data--

Quarter
  ID
  1
  2

Review
  ID     (Other fields, not relevant to join)
  1
  2
  3
  4
  5

Sale
  ID    ReviewID    QuarterID    isArchived (Other fields, not relevant)
  1        1           1            0
  2        2           1            1
  3        2           1            0
  4        1           2            0
  5        5           1            0
  6        5           2            0

Potential
  ID    ReviewID    QuarterID    isArchived (Other fields, not relevant)
  1        1           1            0
  2        3           1            0
  3        4           2            1
  4        4           2            0
  5        5           2            0

Joining the above sample data, I would expect the output to look like:

QuarterID    ReviewID    SaleID    PotentialID
    1           1          1           1
    1           2          3          null
    1           3         null         2
    1           4         null        null
    1           5          5          null
    2           1          4          null
    2           2         null        null
    2           3         null        null
    2           4         null         4
    2           5          6           5

But the problem I am having is I am only returning the rows like the first and last row, where there is both a Sale and Potential for a given Quarter/Review combo, and not the ones where one or many may be null.

Upvotes: 0

Views: 60

Answers (1)

Amit Sukralia
Amit Sukralia

Reputation: 950

Not sure if I understood your question correctly (some sample data will help) but I think you mean that you need all the combinations of Quarter and Review and then any related Sale and Potential data for each combination of Quarter and Review. If that is what you need, then try the below query:

SELECT [Quarter].ID AS QID, Review.ID AS RID, Sales.ID AS SID, Potentials.ID AS PID FROM [Quarter]
CROSS JOIN [Review]
LEFT JOIN (SELECT * FROM Sale WHERE IsArchived = 0) Sales ON [Quarter].ID = Sales.QuarterID AND [Review].ID = Sales.ReviewID
LEFT JOIN (SELECT * FROM Potential WHERE IsArchived = 0) Potentials ON [Quarter].ID = Potentials.QuarterID AND [Review].ID = Potentials.ReviewID

Upvotes: 1

Related Questions