Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    May 2010
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Join table question

    Sometimes JOINS can be confusing. What's the difference with the two queries below? Why it will bring different results?

    Code:
    SELECT PK1.PNN,PK1.NAME,PK2.GBT,SSM,RSM
    				 FROM PK1
    				 left join PK2 ON PK2.PNN = PK1.PNN AND PK2.MANT = PK1.MANT";
    				left join MSS ON dbo.MSS.RSM = PK2.GBT AND dbo.MSS.MANT = PK2.MANT
    				
    				SELECT PK1.PNN,PK1.NAME,PK2.GBT,SSM,RSM  
    				FROM MSS 
    				left join PK2 ON PK2.GBT = MSS.RSM AND MSS.MANT = PK2.MANT 
    				left join PK1 ON PK1.PNN = PK2.PNN AND PK1.MANT = PK2.MANT

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,077
    Thanks
    2
    Thanked 320 Times in 312 Posts
    Your result is different between the two queries because you are using different tables/join conditions in the queries.

    If that's a somewhat vague answer, it's because we don't know what your table definitions are, what relationships exist between the tables, what data is in the relevant tables, what result you got from that data, what result you expected, and what your nondescript table and column names mean.

    I'm sorry, but no one here can really help you with why either one did or didn't produce the result you expected or why the result was different between them without knowing all of this information.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    Only thing CFMaBiSmAd got wrong is where he said you are using different tables: Both queries use the same 3 tables.

    But the ORDERING of the tables SELECTed is VERY important when using LEFT or RIGHT joins.
    And your ordering is completely wrong:
    PK1 left join PK2 left join MSS
    MSS left join PK2 left join PK1

    If you wanted to re-order that tables in the SELECT, you would have needed to use
    MSS right join PK2 right join PK1

    Casual inspection tells me that would do the job, but I'd need to see the table designs to be sure.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Regular Coder
    Join Date
    May 2010
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am wondering why there is a data that second query can't get. This data exist on PK1,PK2 and MSS. The MANT condition is also satisfied. Of course if I replace SELECT PK1.PNN with MSS.RSM, the data will be retrieve. Now there are datas that exists in PK1,Pk2 and MSS, they are retrieved. As i told I am wondering why there are datas that are not retrieved.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,947
    Thanks
    79
    Thanked 4,424 Times in 4,389 Posts
    I told you why. Read my post again.

    You can *NOT* change the order of the SELECTed tables without changing the type of join and expect anything like the same results. Period.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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