Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    33

    Unhappy Unanswered: sql query problem

    I have 2 tables :A and B. the relation is 1:* , i.e. to one record in A may relate 0..n records in B. The primary key of A : 2 fields id, version.
    B has foreing key in each record , it says to wich record in A this record in B related. I'm trying to implement search engine: user chooses any field of A or B and SQL query shoul find results: all apropriate A records and all apropriate B records to each A.

    The following query doesn't work, cause it only finds A records that have 1 or more related B records, and doesn't find A records that have no B records:
    SELECT DISTINCT A.* FROM A,B WHERE (A.id=B.id) AND (A.version = B.version) AND (...users constraints...)

    The following query doesn't work, cause iit finds right:A records have 1 or more related B records wrong:all A records that have no B records, regardless to user constraints:
    SELECT DISTINCT A.* FROM A,B WHERE (...users constraints...)

    I'm really frustrated, unsuccessfully writing this query for 2 days
    Thank you for help

  2. #2
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100

    Re: sql query problem

    My best guess below:

    SELECT DISTINCT A.*
    FROM A
    LEFT JOIN B ON (A.id=B.id) AND (A.version = B.version) AND
    (B table ...users constraints...)
    WHERE (A table ...users constraints...)

    Second Guess

    SELECT DISTINCT A.*
    FROM A
    LEFT JOIN B ON (A.id=B.id) AND (A.version = B.version)
    WHERE (A & B table ...users constraints...)


    Tim S
    Last edited by TimS; 01-18-04 at 17:24.

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    You need to use a left outer join - also, do you have access to sql server books online ? If not, you need to download it from ms - it will help you with all aspects of sql server (including left outer join).

  4. #4
    Join Date
    Nov 2003
    Posts
    33
    Thank you all for your reply
    Is se syntax LEFT JOIN ON ... implyes left outer join or left inner join?

  5. #5
    Join Date
    Sep 2003
    Posts
    39
    Originally posted by yuliam
    Thank you all for your reply
    Is se syntax LEFT JOIN ON ... implyes left outer join or left inner join?
    Left join is a left OUTER join.

  6. #6
    Join Date
    Nov 2003
    Posts
    33

    Thumbs up Re: sql query problem

    Originally posted by TimS
    My best guess below:

    SELECT DISTINCT A.*
    FROM A
    LEFT JOIN B ON (A.id=B.id) AND (A.version = B.version) AND
    (B table ...users constraints...)
    WHERE (A table ...users constraints...)

    Second Guess

    SELECT DISTINCT A.*
    FROM A
    LEFT JOIN B ON (A.id=B.id) AND (A.version = B.version)
    WHERE (A & B table ...users constraints...)


    Tim S
    Thank you very much!
    Your second guess woked

Posting Permissions

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