0

I am trying to write a basic SQL query, but can't quite get it to work.

I am trying to join 3 tables when 1 of the tables may not have a value.

    select 
          distinct(jobnumber), 
          name, 
          reference, 
          DescriptionOption
       from 
          Heading 
             join tracking 
                on heading.jobkeyid = tracking.jobkeyid 
             join Information 
                on Heading.jobkeyid = information.jobkeyid 
       Where 
              StageID < 199 
          and heading.DateInProduction > '2022-07-01 00:00:00.000' 
          and DescriptionOption IN ('Style FF', 'FF')
       order by 
          JobNumber desc

This is what I have done but if the tracking table doesn't have the requested data it doesn't show in the results.

Kaan
  • 5,434
  • 3
  • 19
  • 41
Reynolds15
  • 1
  • 2
    `LEFT JOIN`.... – Barry Carter Aug 03 '22 at 15:31
  • 1
    It sounds like you could use a [good tutorial](https://www.dataquest.io/blog/sql-joins-tutorial/) on joins. – Stu Aug 03 '22 at 15:37
  • 1
    Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) – Igor Aug 03 '22 at 15:42
  • also, best to always qualify table.column (or alias.column) since others dont know your table structures, especially with joins. Which table is the "OPTIONAL" table that may NOT have a matching record? Tracking or Information? Which table for StageId, DescriptionOption, JobNumber. Please EDIT the existing query and fully qualify. – DRapp Aug 03 '22 at 16:50
  • Does this answer your question? [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) – BeRT2me Aug 04 '22 at 05:14
  • Hi DRapp, Sorry I am a complete novice. The Optional table is tracking, the tables are as follows: tracking.StageID, Information.DescriptionOption, Heading.JobNumber. – Reynolds15 Aug 04 '22 at 08:38
  • I hope this makes sense – Reynolds15 Aug 04 '22 at 08:38
  • Hi Barry, I must admit I did try Left Join after doing a bit of Research but it made no difference – Reynolds15 Aug 04 '22 at 08:39
  • Hi Stu, You are not wrong at all, I am not trained in Microsoft SQL, I am just limping along, thank you for the link – Reynolds15 Aug 04 '22 at 08:40
  • select distinct(Heading.jobnumber), Heading.name, Heading.reference, Information.DescriptionOption from Heading join tracking on heading.jobkeyid=tracking.jobkeyid join Information on Heading.jobkeyid = information.jobkeyid Where Tracking.StageID < 199 and heading.DateInProduction > '2022-07-01 00:00:00.000' and Information.DescriptionOption IN ('Style FF', 'FF') order by Heading.JobNumber desc – Reynolds15 Aug 04 '22 at 08:45
  • Hi DRapp, Is this what you wanted me to do? I really appreciate your help. – Reynolds15 Aug 04 '22 at 08:46

0 Answers0