Results 1 to 3 of 3
  1. #1
    Join Date
    May 2005

    Unanswered: is it possible to write in one query???

    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

    (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 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???!!!!



  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    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 | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2005
    Hi r937,

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

    Thanks alot for all your help!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts