#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    95
    Rep Power
    0

    Exclamation LEFT JOIN Returns all rows even if we restrict columns values


    Dear Respected Members,

    I have problem with LEFT JOIN that it brings the whole columns from tables even if I limit to specific column ids I am forced to use the LEFT JOIN because in table B - column "id" - is not always exists in table A.

    Please could you advise how can I do the following:

    I have two tables (Table A and Table B).

    Table A as below:

    id colA1 colA2
    == ==== ====
    1 x y
    2 x y
    3 x y
    4 x y
    5 x y
    6 x y
    7 x y


    Table B as below:

    id colB1 colB2
    == ==== ====
    2 a a
    2 a b
    2 a c
    4 b d
    4 b e

    I want to LEFT JOIN both tables to get the result as below:

    table A join table B:

    id colA1 colA2 colB1 colB2
    == ===== ===== ===== =====
    1 x y - -
    2 x y a a
    2 x y a b
    2 x y a c
    3 x y - -
    4 x y a d
    4 x y a e
    5 x y - -



    I made this query:

    $sql = 'SELECT A.colA1, A.colA2, B.colB1, B.colB2 FROM
    A LEFT JOIN B ON
    A.id=B.id AND
    A.id IN(1,2,3,4,5)';

    The problem this query returns LEFT JOIN of all rows (1,2,3,4,5,6,7), Why? How can I restrict to first 5 rows?

    Thanks.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    try like this --
    Code:
    SELECT A.colA1
         , A.colA2
         , B.colB1
         , B.colB2 
      FROM A 
    LEFT 
      JOIN B 
        ON B.id = A.id 
     WHERE A.id IN (1,2,3,4,5)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Posts
    95
    Rep Power
    0
    Originally Posted by r937
    try like this --
    Code:
    SELECT A.colA1
         , A.colA2
         , B.colB1
         , B.colB2 
      FROM A 
    LEFT 
      JOIN B 
        ON B.id = A.id 
     WHERE A.id IN (1,2,3,4,5)
    Dear r937,

    Thanks so much! It worked fine!!! I really appreciate your help! You saved my time!!!

    Kind regards,

IMN logo majestic logo threadwatch logo seochat tools logo