Hey there! Sign in to join this conversationNew here? Join for free
    • Thread Starter
    Offline

    17
    ReputationRep:
    Hey all,

    I'm trying to write an SQL statement which links one primary (OSLD1F3.OEP20) table to 3 secondary tables (oslrggx3.oep76u, oslslf3.slp05, osld1f3.oep21) with each link having multiple joins. Whilst the query runs fine and reports, some expected entries are missing. Can you please check out the code below and let me know where I've gone wrong? I only know a little SQL and this is well beyond me tbh!

    Code:
    select cusn20, dseq20, cnam05,ccls20, regn20,stte20, trty20, slmn20, list20, b00176, b00276, b00376, b00476, b00576, b00676, b00776, b00876, b00976, b01076, b01176,  b01276, b01376, b01476, b01576, b01676, b01776, b01876, b01976, b02076, b02176, b02276, b02376, b02476, b02576, b02676, cad105, cad205, cad305, cad405, cad505, pcd105, pcd205
    
    from osld1f3.oep20
    
    inner join oslrggx3.oep76u
           on osld1f3.oep20.cono20 = oslrggx3.oep76u.cono76
           and osld1f3.oep20.list20 = oslrggx3.oep76u.list76
    
    inner join oslslf3.slp05
    on osld1f3.oep20.cono20 = oslslf3.slp05.cono05
           and osld1f3.oep20.cusn20 = oslslf3.slp05.cusn05
           and osld1f3.oep20.dseq20 = oslslf3.slp05.dseq05
    
    inner join osld1f3.oep21
    on osld1f3.oep20.cono20 = osld1f3.oep21.cono21
           and osld1f3.oep20.cusn20 = osld1f3.oep21.cusn21
           and osld1f3.oep20.dseq20 = osld1f3.oep21.dseq21
    
    where osld1f3.oep20.cono20='CL'
           and  oslrggx3.oep76u.dtlc76='0'
           and osld1f3.oep20.stte20 in('A1','A5','A9','A3')
    
    order by osld1f3.oep20.cusn20, osld1f3.oep20.dseq20
    thanks!
    • Offline

      14
      What expected entries are missing? I'd need a little more explanation I think. You're using inner joins, which would mean that you only get data common to both tables you're linking.
      • Thread Starter
      Offline

      17
      ReputationRep:
      (Original post by ch0llima)
      What expected entries are missing? I'd need a little more explanation I think. You're using inner joins, which would mean that you only get data common to both tables you're linking.
      I'm using 4 tables here.

      Basically, I'm trying to translate a query running in AS400 Query (a system designed in the 80s, but still in use and making something of a comeback). I've got the same tables selected, WHERE criteria, fields and sorting. I'm pretty sure it's my joins to the table osld1f3.oep20 that are the problem. I've used the same keys for each join to osld1f3.oep20, but I'm not sure that the query is reading it as that. I have a feeling it's running the query from osld1f3.oep20 and oslrggx3.oep76u, then joining the next table, rerunning for that, then joining the final table before outputting.

      I'm at home at the moment, so I can't really do much more! I'll get back to you on this tomorrow. Thanks for your help so far though!
      Offline

      19
      ReputationRep:
      (Original post by ch0llima)
      What expected entries are missing? I'd need a little more explanation I think. You're using inner joins, which would mean that you only get data common to both tables you're linking.
      I think that's the most likely thing. explanation http://www.sqlbook.com/SQL/Outer-Join-39.aspx

      You've got to decide whether you'd prefer getting a null value or no row at all for every join.
      If you suddenly get loads more rows than you want you have to make the selections more exclusive.
      Offline

      19
      ReputationRep:
      (Original post by mikeyd85)
      I'm using 4 tables here.

      Basically, I'm trying to translate a query running in AS400 Query (a system designed in the 80s, but still in use and making something of a comeback). I've got the same tables selected, WHERE criteria, fields and sorting. I'm pretty sure it's my joins to the table osld1f3.oep20 that are the problem. I've used the same keys for each join to osld1f3.oep20, but I'm not sure that the query is reading it as that. I have a feeling it's running the query from osld1f3.oep20 and oslrggx3.oep76u, then joining the next table, rerunning for that, then joining the final table before outputting.

      I'm at home at the moment, so I can't really do much more! I'll get back to you on this tomorrow. Thanks for your help so far though!
      PS that sort of consideration shouldn't affect the number or content of rows in SQL - it's like a venn diagram

      A n B n C has the same contents whether you work A n B out first or A n C
     
     
     
  1. See more of what you like on The Student Room

    You can personalise what you see on TSR. Tell us a little about yourself to get started.

  2. Poll
    Did TEF Bronze Award affect your UCAS choices?
    Useful resources
  3. See more of what you like on The Student Room

    You can personalise what you see on TSR. Tell us a little about yourself to get started.

  4. The Student Room, Get Revising and Marked by Teachers are trading names of The Student Room Group Ltd.

    Register Number: 04666380 (England and Wales), VAT No. 806 8067 22 Registered Office: International House, Queens Road, Brighton, BN1 3XE

    Quick reply
    Reputation gems: You get these gems as you gain rep from other members for making good contributions and giving helpful advice.