CODEWITHSUNDEEP
mysqlsql
Algy Taylor
Algy Taylor

Reputation: 834

SQL Query to match unlinked data

Say I have three tables:

TABLE A

idA     variable
1       Number of hats
2       Number of scarves
3       Number of mittens


TABLE B

idB     name
1       Andy
2       Betty
3       Cedric
4       Daphne


TABLE C

idA     idB     value
1       1       15
1       2       2
1       3       89
2       1       10
2       3       3
2       4       1504
3       2       12
3       3       4
3       4       1

Looking at the table, it's relatively simple to work out - we know how many hats (2) and mittens (12) that she owns, but not how many scarves. Likewise, for Daphne we know how many scarves (1504) and mittens (1) she owns, but not the amount of hats.

However, I'd like a list of fields that there ISN'T information for - I would have a returned result looking something like this (if I asked for Andy)

idA     variable
3       Number of mittens

Any idea how I do that? :)

Upvotes: 0

Views: 180

Answers (3)

OGHaza
OGHaza

Reputation: 4795

The following query works:

SELECT B.name, A.variable
FROM B
CROSS JOIN A
LEFT JOIN C ON C.idA = A.idA AND C.idB = B.idB
WHERE C.value IS NULL

Its the CROSS JOIN that is key, it says JOIN every record in B to every record in A. Once you've done that you can easily check which combinations of idA and idB don't have a corresponding record in C.

Tested on SQLFiddle

Result:

NAME       UNKNOWN VARIABLE
-------------------------------
Andy       Number of mittens
Betty      Number of scarves
Daphne     Number of hats

Upvotes: 1

ray
ray

Reputation: 457

select idA, variable
from a
where idA not in (select idA from c where idB = 1)

Upvotes: 1

T0to
T0to

Reputation: 422

You can use joins to associate 2 tables.

In your case, if you ask for Andy and you wanna know the number of mittens, you'll have:

SELECT name, value 
FROM B
INNER JOIN C on B.idB = C.idB
WHERE id.A = 3

Responding to your comment, you try something like that:

SELECT name, variable
FROM B
RIGHT JOIN C on B.idB = C.idB
RIGHT JOIN A on C.idA = A.idA
WHERE C.idA IS NULL

Upvotes: 1

Related Questions