Selecting A Set Not In Another (composite key) 2

[mordja]

Hi,

I have a key which is comprised of three different fields.
I also have a list of all unique keys used in my db. I need to find out which keys from that list are not in use. I know that not IN can only be used on a singular value. What strategy can I use to find this out.

Thanks

Mordja

 

[lupins46]

'I ..have a list of all unique keys used in my db'
OK
'which keys from that list are not in use'
?Eh?

 

[lespaul]

Have you looked at the EXISTS keyword?


Leslie

 

[mordja]

Sorry,

The keys in the list of composite keys belong to several different tables. I would like to find out which keys in the list are not contained within the tables.

Mordja

 

[lespaul]

A key is a field or combination of fields in a SINGLE table that together are unique. How can you have a composite key that belongs to several different tables?

Leslie

 

[mordja]

lespaul,

Call it a foreign key if you like. The point is that I have a key/foreign key, say {a,b,c}. I have a complete list of all different values that the key{a,b,c} may take. I have three tables for which {a,b,c} is the primary key. I would like to find out which values of {a,b,c} are not in each of my three different tables.

Thanks

Mordja

 

[lupins46]

If we call the main table tblKeys anf the other tables tbl1, tbl2, tbl3 the you can find which keys are missing from tbl1 using this sort of query:

Select tblkeys.* from tblkeys inner join tbl1
On tblkeys.keyfield1 = tbl1.keyfield1 and tblkeys.keyfield2 = tbl1.keyfield2 and tblkeys.keyfield3 = tbl1.keyfield3
where tbl1.anyfield is null

 

[r937]

if you have this list of keys, the easiest thing would be to store them in a table

then you can run some really simple queries to find the data you want

let me know once you've saved the table, what it's called, and what the table/column names for the other three tables are

rudy

http://r937.com/

SQL Consulting

 

[PHV]

List of keys not used in any table:
SELECT L.a, L.b, L.c
FROM tblListKeys L LEFT JOIN (
SELECT a, b, c FROM Table1
UNION SELECT a, b, c FROM Table2
UNION SELECT a, b, c FROM Table3
) U ON L.a=U.a AND L.b=U.b AND L.c=U.c
WHERE U.a Is Null;
List of keys not used in each table:
SELECT "Table1" As TableName, L.a, L.b, L.c
FROM tblListKeys L LEFT JOIN Table1 T ON L.a=T.a AND L.b=T.b AND L.c=T.c
WHERE T.a Is Null
UNION SELECT "Table2", L.a, L.b, L.c
FROM tblListKeys L LEFT JOIN Table2 T ON L.a=T.a AND L.b=T.b AND L.c=T.c
WHERE T.a Is Null
UNION SELECT "Table3", L.a, L.b, L.c
FROM tblListKeys L LEFT JOIN Table2 T ON L.a=T.a AND L.b=T.b AND L.c=T.c
WHERE T.a Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mordja]

Lupins,

Thanks that worked well.

PHV,

Worked well as well, nice to know which table is missing the key.

Mordja