Hey all

Just wondering whether it is possible to do the following in one query - if so how would i go about it?

Any help will be very much appreciated

There are three tables: Table A, B, C for example. Table B is a junction table that splits up the many to many relationship between Tables A and C.

What I would like to do is to display all the records from table A which do not exist for a given value in Table C (inputted parameter).

Example if the tables have the following values :
A {1,2,3,4,5}
C {A,B,C}
B {1,A
2,A
2,B
1,C}

(note table b has 2 cols - so first row in table b reads 1, a)

If ‘a’ was input parameter query should return 3,4,5
‘B’ would return 1,3,4,5
‘C’ returns 2,3,4,5.

My query as it currently stands:

Select a.* from a left join b on a.id=b.col1 where b.col2<>’parameter’;

So if a was the input parameter it would display all the records from table A - although I don’t want it to display number 2 or number 1 but they appear as other records from the junction table relate to them. How can I resolve this in one query???-if it is possible???

Hope this makes sense???!!!!

Thanks!

Sub.

yes, it's possible, and there are several ways to do it

here's one way: do a cross join between A and C, to give you all possible combinations, then add a left outer join to B, matching on both columns, with a WHERE clause to check IS NULL for unmatched rows

Hi r937,

Thanks - used your advice and got the results that I wanted

Thanks alot for all your help!!!