SQL Joining

[Albatross]

My SQL is getting rusty

I have table tblA with numbers in it :

tblA.f1
-------
1
2
3

And tblB also with numbers in it :

tblB.f2
-------
11
22

3 entries + 2 entries = 6 entries when I "SELECT tblA.f1, tblB.f2 from tblA, tblB;"

1 11
1 22
2 11
2 22
3 11
3 22

now !!! in a table I have the entries I "Don't Want To See"

tblC
f1 f2
--------
1 11
3 22


How can I SQL these three tables into showing a list of 4 of the 6 entries :

1 22 --- Note that {1 11} and {3 22} are not present because they are defined in tblC
2 11
2 22
3 11

I suspect something really strange is necessary, such as a JOIN.

[M Owen]

Check out the LEFT OUTER JOIN ...

[Albatross]

can you be a bit more explicit?

[Thread1]

How about this one :D

SELECT tblA.f1, tblB.f2 FROM tblA CROSS JOIN tblB
WHERE NOT EXISTS (SELECT f1 FROM tblC WHERE f1 = tblA.f1 AND f2 = tblB.f2)

The cross join appears to be the same with SELECT * FROM tblA, tblB..

[M Owen]

Chay,

Nifty one that CROSS JOIN ... There is a caveat with it tho ... It is supported as far as I can tell on SQL2 ... We don't know what DB or version this person is using ... Access has about the same with a UNION query ...

- Mike

[Albatross]

A bus driver showed me this :

Select tblA.f1, tblB.f2 from tblA, tblB; => put it into a temporary table, a view.
call the the view or table 'TT'

now
SELECT TT.f1, TT.f2, tblC.f1, tblC f2
FROM TT left outer join tblC
ON tlbC.f1 = TT.f1 and
tblC.f2 = TT.f2

this renders


TT.f1 TT.f2 tblC.f1 tblC.f2
-----------------------------------
1 11 1 11
1 22 null null
2 11 null null
2 22 null null
3 11 null null
3 22 3 22

the nulls pop up because the second table tblC is smaller than the first TT

but those are the records we are looking for so

SELECT TT.f1, TT.f2, tblC.f1, tblC f2
FROM TT left outer join tblC
ON tlbC.f1 = TT.f1 and
tblC.f2 = TT.f2
where tblC.f1 is NULL


TT.f1 TT.f2 tblC.f1 tblC.f2
-----------------------------------
1 22 null null
2 11 null null
2 22 null null
3 11 null null

clean it up and :
SELECT TT.f1, TT.f2
FROM TT left outer join tblC
ON tlbC.f1 = TT.f1 and
tblC.f2 = TT.f2
where tblC.f1 is NULL


TT.f1 TT.f2
----------------
1 22
2 11
2 22
3 11

Solution found