Unanswered: is it possible to write in one query???
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 :
(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???
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