CODEWITHSUNDEEP
sqlsql-serverdatabase
Bruno
Bruno

Reputation: 4655

SQL query that is a mix of a LEFT join and an INNER join

I have two tables :

TableA        TableB
id            id
amt           amt
idUser        idTableA

Let's say the following data is in TableA :

1   10  1
2   20  1
3   30  1
4   40  2
5   50  3
6   60  4
7   70  4

and the following data is in TableB :

1   10  1
2   20  2
3   21  2
4   51  5
5   70  7

All fields are not nullable.

if I want to know what items in TableA have no 'link' to tableB (through the TableB.idTableA field), I can do this query :

SELECT a.id, a.amt
FROM TableA a LEFT JOIN TableB b on b.idTableA = a.id
WHERE b.idTableA IS NULL

and it will return the following :

3   30
4   40
6   60

if I want to know what items in TableA (that do have a link to tableB) for which the amt values are not identical in each table I can do :

SELECT a.id, a.amt, b.amt
FROM TableA a INNER JOIN TableB b on b.idTableA = a.id
WHERE a.amt <> b.amt

and it will return :

2   20  21
5   50  51

Now, I would like a query that does the following : Return the idUsers for which only some of their entries in TableA have a link with TableB (meaning that for the idUser, there must also be some entries in the TableA with no link between the two tables) and for which at least one entry has different amounts between the two tables among the lines that are linked.

In my example, the idUser 1 would be returned by such a query because the third entry in TableA fulfills the first condition (some entries with no link) and there exists an entry for which amounts are different (linked with the value 2 for idTableA are amounts 20 in TableA vs 21 in TableB)

userId 2 will not be returned because it has no line linked between the two tables,

userId 3 will not be returned because it does not have a line in TableA without link in TableB

userId 4 will not be returned because although it has 1 line in TableA without link and also 1 line with a link, the line with a link has its amounts identical between the 2 tables.

So really it is a mix of the two initial queries...

Thanks for your input and my apologies for the basic formatting and language, I hope this is clear enough :)

Upvotes: 2

Views: 471

Answers (2)

Isaac Fife
Isaac Fife

Reputation: 1689

SELECT a.idUser
FROM
(SELECT a2.id, a2.amt, a2.idUser 
    FROM TableA AS a2 
    LEFT JOIN TableB AS b2 
    ON b2.idTableA = a2.id 
    WHERE b2.idTableA IS NULL
) AS a 
INNER JOIN TableB AS b
ON b.idTableA = a.id
WHERE a.amt <> b.amt;

I'm not sure if this is actually any more efficient, but here is how I would have done it.

Upvotes: 1

therealmitchconnors
therealmitchconnors

Reputation: 2758

with loners as (
SELECT a.*
FROM TableA a 
LEFT JOIN TableB b on b.idTableA = a.id
WHERE b.idTableA IS NULL
), 
diffs as (
SELECT a.*
FROM TableA a 
INNER JOIN TableB b on b.idTableA = a.id
WHERE a.amt <> b.amt
)
select loners.userID
FROM loners
INNER JOIN diffs on LONERS.userID = DIFFS.userID

Upvotes: 4

Related Questions