SitePoint Sponsor

User Tag List

Results 1 to 19 of 19

Thread: Query LEFT JOIN

Hybrid View

  1. #1
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query LEFT JOIN

    Hi everyone.

    I have this situation:

    tbl_A ( Suppliers ):

    Code:
    IDSUP	SUP	
    1	MULTI
    2	SUPER
    3	DEVEL
    ...
    tbl_B ( Delivery)

    Code:
    ID	IDSUP	SUP	DATE_DELIVERY
    1	1	MULTI	2008-04-29
    2	2	SUPER	2008-04-29
    3	3	DEVEL	2008-04-29
    4	1	MULTI	2008-04-30
    5	3	DEVEL	2008-04-30
    6	1	MULTI	2008-05-02
    7	2	SUPER	2008-05-02
    I execute this query SQL:

    Code:
    SELECT A.IDSUP, A.SUP, B.DATE_DELIVERY
    FROM tbl_A A
    LEFT JOIN tbl_B B
    ON B.IDSUP = A.IDSUP
    WHERE B.DATE_DELIVERY = '2008-05-02'
    But I don't have this output:

    Code:
    IDSUP	SUP	DATE_DELIVERY
    1	MULTI	2008-05-02
    2	SUPER	2008-05-02
    3	DEVEL	NULL

    Can you help me?
    Thamks, regards
    Viki

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    First, why have you duplicated the column SUP in 2 tables?
    Second, you won't get the line with NULL, because you're using a WHERE clause with a field from the second table.
    Put it in the ON instead
    Code:
    SELECT A.IDSUP, A.SUP, B.DATE_DELIVERY
    FROM tbl_A A
    LEFT JOIN tbl_B B
    ON B.IDSUP = A.IDSUP
    AND B.DATE_DELIVERY = '2008-05-02'

  3. #3
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    First, why have you duplicated the column SUP in 2 tables?
    Second, you won't get the line with NULL, because you're using a WHERE clause with a field from the second table.
    Put it in the ON instead
    Code:
    SELECT A.IDSUP, A.SUP, B.DATE_DELIVERY
    FROM tbl_A A
    LEFT JOIN tbl_B B
    ON B.IDSUP = A.IDSUP
    AND B.DATE_DELIVERY = '2008-05-02'
    thanks x your reply.

    My output must be:

    1) list of tbl_B Suppliers who have already delivered today... and
    2) list of tbl_A Suppliers which today have not delivered...


  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Didn't the corrected query I posted work?

  5. #5
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Didn't the corrected query I posted work?
    I changed it:

    Code:
    SELECT A.IDSUP, A.SUP, B.DATE_DELIVERY
    FROM tbl_A A
    LEFT JOIN tbl_B B
    ON B.IDSUP = A.IDSUP
    AND B.DATE_DELIVERY = '2008-05-02'
    Output:

    Code:
    IDSUP	SUP	DATE_DELIVERY
    1	MULTI	2008-05-02
    2	SUPER	2008-05-02

    I changed it:

    Code:
    SELECT A.IDSUP, A.SUP, B.DATE_DELIVERY
    FROM tbl_A A
    LEFT JOIN tbl_B B
    ON B.IDSUP = A.IDSUP
    Output:

    Code:
    IDSUP	SUP	DATE_DELIVERY
    1	MULTI	2008-05-02
    2	SUPER	2008-05-02
    3	DEVEL	NULL

    The second Output is right... but no date of today...

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Strange. Mine should give you what your second query gives.
    And this?
    Code:
    SELECT A.IDSUP, A.SUP, C.DATE_DELIVERY
    FROM tbl_A A
    LEFT JOIN 
      (SELECT B.IDSUP, B.DATE_DELIVERY
       FROM tbl_B B
       WHERE B.DATE_DELIVERY = '2008-05-02') AS C
    ON C.IDSUP = A.IDSUP

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    viki, you did somethiing wrong

    here is the test to prove the query:
    Code:
    CREATE TABLE Suppliers
    ( idsup INTEGER NOT NULL 
    , sup   VARCHAR(9)
    );
    INSERT INTO Suppliers VALUES
     ( 1 , 'MULTI' )
    ,( 2 , 'SUPER' )
    ,( 3 , 'DEVEL' )
    ;
    CREATE TABLE Delivery
    ( id    INTEGER NOT NULL 
    , idsup INTEGER NOT NULL 
    , sup   VARCHAR(9)
    , date_delivery DATE
    );
    INSERT INTO Delivery VALUES
     ( 1 , 1 , 'MULTI' , '2008-04-29' )
    ,( 2 , 2 , 'SUPER' , '2008-04-29' )
    ,( 3 , 3 , 'DEVEL' , '2008-04-29' )
    ,( 4 , 1 , 'MULTI' , '2008-04-30' )
    ,( 5 , 3 , 'DEVEL' , '2008-04-30' )
    ,( 6 , 1 , 'MULTI' , '2008-05-02' )
    ,( 7 , 2 , 'SUPER' , '2008-05-02' )
    ;
    SELECT A.IDSUP, A.SUP, B.DATE_DELIVERY
      FROM Suppliers A
    LEFT OUTER
      JOIN Delivery B
        ON B.IDSUP = A.IDSUP
       AND B.DATE_DELIVERY = '2008-05-02'
    
    IDSUP SUP    DATE_DELIVERY
      1   MULTI  2008-05-02
      2   SUPER  2008-05-02
      3   DEVEL  NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Thx r937

  9. #9
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks, your query working in the db mysql and not working in the page ASP...

    Code:
       SQL = " SELECT "
       SQL = SQL & " A.IDSUP, A.SUP, B.DATE_DELIVERY, "
       SQL = SQL & " , MIN(B.SUP) MIN_SUP"
       SQL = SQL & " FROM "   
       SQL = SQL & " Suppliers A "
       SQL = SQL & " LEFT OUTER JOIN "
       SQL = SQL & " Delivery B "
       SQL = SQL & " ON B.IDSUP = A.IDSUP "
       SQL = SQL & " AND B.DATE_DELIVERY = '2008-05-02' "
    Output in the db mysql:

    Code:
    IDSUP SUP    DATE_DELIVERY
      1   MULTI  2008-05-02
      2   SUPER  2008-05-02
      3   DEVEL  NULL
    Output in the page ASP:

    Code:
    IDSUP SUP    DATE_DELIVERY
      1   MULTI  2008-05-02
      2   SUPER  2008-05-02
    If delete in the query:

    Code:
    SQL = SQL & " AND B.DATE_DELIVERY = '2008-05-02' "
    The output is right:

    Code:
    IDSUP SUP    DATE_DELIVERY
      1   MULTI  2008-05-02
      2   SUPER  2008-05-02
      3   DEVEL  NULL
    The problem:

    I have to extract from the table "Delivery" the supplier = DEVEL even when not delivery for today...


  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you changed the query again!!! this time you added MIN() without a GROUP BY!!!

    and have you tested that query in mysql? because it doesn't work -- you have consecutive commas, and that's a syntax error

    please, always make sure your query is working in mysql before dropping it into your application code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, sorry...

    I try your query in the DB MYSQL and your query working... no problem...

    The problem is with your query paste in the code ASP with added MIN() with a GROUP BY....

    You understand ?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by viki1967 View Post
    The problem is with your query paste in the code ASP with added MIN() with a GROUP BY....

    You understand ?
    yes, i do

    my advice still holds -- always test your query in mysql first
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes, i do

    my advice still holds -- always test your query in mysql first
    This query working in MySQL and ASP page...

    Code:
    SELECT A.IDSUP, A.SUP, MIN(B.SUP) MIN_SUP, 
        CASE
        WHEN B.DATE_DELIVERY <> '2008-05-02' THEN 'NULL'
        END AS DATE_DELIVERY
    FROM Suppliers A
    LEFT JOIN Delivery B
    ON B.IDSUP = A.IDSUP
       GROUP BY 
       B.IDSUP
       ORDER BY B.DATE_DELIVERY
    Do you think ?

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, that's faulty

    do this instead --
    Code:
    SELECT A.IDSUP
         , A.SUP
         , MIN(B.SUP) MIN_SUP
         , B.DATE_DELIVERY 
      FROM Suppliers A
    LEFT OUTER
      JOIN Delivery B
        ON B.IDSUP = A.IDSUP
       AND B.DATE_DELIVERY = '2008-05-02' 
    GROUP 
        BY A.IDSUP
    ORDER 
        BY B.DATE_DELIVERY
    do you understand why the DATE_DELIVERY condition is in the ON cluase of the JOIN instead of in the WHERE clause?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Because if I write this:

    Code:
    SELECT A.IDSUP
         , A.SUP
         , MIN(B.SUP) MIN_SUP
         , B.DATE_DELIVERY 
      FROM Suppliers A
    LEFT OUTER
      JOIN Delivery B
        ON B.IDSUP = A.IDSUP
       AND B.DATE_DELIVERY = '2008-05-02' 
    GROUP 
        BY A.IDSUP
    ORDER 
        BY B.DATE_DELIVERY
    The output is:

    Code:
    IDSUP SUP    DATE_DELIVERY
      1   MULTI  2008-05-02
      2   SUPER  2008-05-02
    Not is:

    Code:
    IDSUP SUP    DATE_DELIVERY
      1   MULTI  2008-05-02
      2   SUPER  2008-05-02
      3   DEVEL  NULL

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    why did you change AND to WHERE

    my query has
    Code:
    LEFT OUTER
      JOIN Delivery B
        ON B.IDSUP = A.IDSUP
       AND B.DATE_DELIVERY = '2008-05-02'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, but I change your AND to WHERE because this syntax:

    Code:
    SELECT A.IDSUP
         , A.SUP
         , MIN(B.SUP) MIN_SUP
         , B.DATE_DELIVERY 
      FROM Suppliers A
    LEFT OUTER
      JOIN Delivery B
        ON B.IDSUP = A.IDSUP
       AND B.DATE_DELIVERY = '2008-05-02' 
    GROUP 
        BY A.IDSUP
    ORDER 
        BY B.DATE_DELIVERY
    The output is:

    Code:
    IDSUP SUP    DATE_DELIVERY
      1   MULTI  2008-05-02
      2   SUPER  2008-05-02
    Instead this query:

    Code:
    SELECT A.IDSUP, A.SUP, MIN(B.SUP) MIN_SUP, 
        CASE
        WHEN B.DATE_DELIVERY <> '2008-05-02' THEN 'NULL'
        END AS DATE_DELIVERY
    FROM Suppliers A
    LEFT JOIN Delivery B
    ON B.IDSUP = A.IDSUP
       GROUP BY 
       B.IDSUP
       ORDER BY B.DATE_DELIVERY

    The output is:

    Code:
    IDSUP SUP    DATE_DELIVERY
      1   MULTI  2008-05-02
      2   SUPER  2008-05-02
      3   DEVEL  NULL
    that wrong ?

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yup, that wrong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yup, that wrong
    I not understand you...


Bookmarks

Posting Permissions

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