Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 952
  • Last Modified:

SQL 2000 Joins and NULL values

I have 3 tables similar to the following...

Parts
Part Number
Condition
Status

Shipments
Tray #
Lid #
Dept
To Location
From Location

Receipts
Part Number
Rec From
Rec To
Notes

I'd like to create a view in SQL 2000 that would join the shipment & receipts tables to the part table and insert NULLs where there are no records. For the Shipments table, the part number could show up in the Tray or Lid column.  I need something like the following...

Part#    Condition    Status      Dept       To Loc        From Loc         Rec From        Rec To         Notes
T1                  2              GOOD      SM           TAV              BC                    NULL           NULL              NULL
T1                  2              GOOD      CT            MIT              BC                    NULL           NULL              NULL
T1                  2              GOOD      NULL        NULL           NULL               TAV                BC                  a note about this receipt
T1                  2              GOOD      NULL        NULL           NULL               MIT                BC                  another note here



Instead, I'm getting this...

Part#    Condition    Status      Dept       To Loc        From Loc         Rec From        Rec To         Notes
T1                  2              GOOD      SM           TAV              BC                    TAV             BC              a note about this receipt
T1                  2              GOOD      CT            MIT              BC                    TAV             BC             a note about this receipt
T1                  2              GOOD      SM           TAV              BC                   MIT                BC           another note here
T1                  2              GOOD      CT            MIT              BC                   MIT                BC           another note here


What's the best way to join these tables to get the correct result?  

Thanks.
~bruno71







0
bruno71
Asked:
bruno71
  • 4
  • 3
  • 3
  • +1
1 Solution
 
Ephraim WangoyaCommented:

Use a LEFT JOIN
0
 
Ephraim WangoyaCommented:
Try this way
select * --add field you want
from Parts
left join Shipments on (Shipments.[Lid #] = Parts.[Part Number] or Shipments.[Tray #] = Parts.[Part Number])
left join Receipts on (Receipts.[Part Number] = Shipments.[Lid #] or Receipts.[Part Number] = Shipments.[Tray #])

Open in new window

0
 
HainKurtSr. System AnalystCommented:
try

select
  r.[Part Number] [Part#], r.Condition, r.Status, s.Dept,
  case when s.[From Loc] is null then 'NULL' else s.[From Loc] end [From Loc],
  ... similar to above for all other columns that you want NULL...
from parts p left join Receipts r on p.[Part Number]=r.[Part Number]
left join Shipments s on (s.[Tray #]=p.[Part Number] or s.[Lid #]=p/[Part Number])
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
bruno71Author Commented:
LEFT JOIN still produces the same results.  Here is what I have...
SELECT     {fields]
FROM         Parts
	left outer join Shipments on  (Shipments.Tray# = Parts.Part# OR Shipments.Lid# = Parts.Part#)
	left outer join Receipts on (Receipts.Part# = Parts.Part#)

Open in new window

0
 
HainKurtSr. System AnalystCommented:
did you try mine? 34898624

0
 
Ephraim WangoyaCommented:
That should be correct, if you are getting data for all fields, it just means you have matching records for reciepits and shipment as well
0
 
HainKurtSr. System AnalystCommented:
I guess I got the issue, lets join Shipments twice instead of "OR"

try this:

select
  r.[Part Number] [Part#], r.Condition, r.Status, s.Dept,
  case when s.[From Loc] is null then 'NULL' else s.[From Loc] end [From Loc],
  ... similar to above for all other columns that you want NULL...
from parts p
left join Receipts r on p.[Part Number]=r.[Part Number]
left join Shipments st on st.[Tray #]=p.[Part Number]
left join Shipments sl on sl.[Lid #]=p.[Part Number]
0
 
bruno71Author Commented:
HainKurt...yours didn't work.

ewangoya....There are matching records for shipments & receipts, but it's duplicating the data.  For example, I have one part with 2 shipment records and 2 receipt records.  In the results, I should have 4 records...all 4 records will have the 'Parts' table fields, 2 records should have the shipment info with NULL receipt fields, and 2 records should have the receipt info with NULL shipment fields.  
- Parts, Ship 1, NULL
- Parts, Ship 2, NULL
- Parts, NULL, Rec 1
- Parts, NULL, Rec 2


Instead, I get 4 records like so...
- Parts, Ship 1, Rec 1
- Parts, Ship 2, Rec 1
- Parts, Ship 1, Rec 2
- Parts, Ship 2, Rec 2
0
 
PSSUserCommented:
Isn't just a case of needing to use a UNION statement?

SELECT     P.[Part Number], P.[Condition], P.[Status], S.[Dept], S.[To Location], S.[From Location], NULL as [Rec From], NULL as [Rec To], NULL as [Notes]
FROM         Parts P LEFT OUTER JOIN Shipments S ON (S.[Tray #] = P.[Part Number] OR S.[Lid #] = P.[Part Number])
UNION
SELECT     P.[Part Number], P.[Condition], P.[Status], NULL as [Dept], NULL as [To Location], NULL as [From Location], R.[Rec From], R.[Rec To], R.[Notes]
FROM         Parts P LEFT OUTER JOIN Receipts R ON R.[Part Number] = P.[Part Number]
ORDER BY [Part Number], [To Location] DESC, [Rec From] DESC
--Sort order optional :)
0
 
HainKurtSr. System AnalystCommented:
whats the result of

select *
from parts p
left join Receipts r on r.[Part Number]=p.[Part Number]
left join Shipments st on st.[Tray #]=p.[Part Number]
left join Shipments sl on sl.[Lid #]=p.[Part Number]

why this did not work?
0
 
bruno71Author Commented:
PSSUser...I think your solution worked.  I got an error that the ORDER BY items had to be in the SELECT statement (which they were).  I got around it by doing...

SELECT * FROM
(...SELECTS & UNION here...)
AS TmpTable
ORDER BY [Part Number]

Thanks for your help.

~bruno71
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now