How to JOIN 2 tables into one query using fields in third table?

Question

I'm trying to solve task for last hours...

I have four tables:

Users (ID INT, Fullname VARCHAR(255))

Segments (Value VARCHAR(255))

Report_1 (ID INT, UserID INT, Segment VARCHAR(255), Total INT)

Report_2 (ID INT, UserID INT, Segment VARCHAR(255), Total INT)

...and I need to get reports by all users with all possible segments.

But reports from "Report_1", "Report_2" may not have records for some users or segments.

The result query must have this columns:

UserID, Segment, Report_1.Total(OR NULL), Report_2.Total(OR NULL)

Could I use CROSS JOIN / APPLY or something?

I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". — John Saunders
– John Saunders, Commented Mar 19, 2014 at 14:45

Dan · Accepted Answer · 2014-03-19 14:36:35Z

3

A simple cartesian join with an outer join should do the trick:

SELECT Users.ID AS [UserID], Segments.Value AS [Segment], Report_1.Total, Report_2.Total
FROM Users, Segments
LEFT OUTER JOIN Report_1 ON 
    Report_1.UserID = Users.ID AND Report_1.Segment = Segments.Value
LEFT OUTER JOIN Report_2 ON 
    Report_2.UserID = Users.ID AND Report_2.Segment = Segments.Value

answered Mar 19, 2014 at 14:36

Dan

10.8k27 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kidig Over a year ago

I have an error on SQL Server 2012 for real-world query. So I forced to use: FROM Users CROSS JOIN Segments instead of commas...

kidig · Accepted Answer · 2014-03-19 14:45:03Z

0

Best that I can suggest is:

SELECT
    U.UserID,
    S.Value,
    ISNULL(R1.Total, 0),
    ISNULL(R2.Total, 0)
FROM
    Users U
CROSS APPLY (
    SELECT DISTINCT Value FROM Segment
) S
LEFT JOIN (
    SELECT
        SUM(Total) AS Total,
        Segment,
        UserID
    FROM
        Report_1
    GROUP BY
        Segment,
        UserID
) R1 ON
    R1.UserID = U.UserID AND
    R1.Segment = S.Value
LEFT JOIN (
        SELECT
        SUM(Total) AS Total,
        Segment,
        UserID
    FROM
        Report_2
    GROUP BY
        Segment,
        UserID
) R2 ON
    R2.UserID = U.UserID AND
    R2.Segment = S.Value

I hope that is good usage for CROSS APPLY...

answered Mar 19, 2014 at 14:45

kidig

2112 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to JOIN 2 tables into one query using fields in third table?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related