Solved

SQL 2000 Joins and NULL values

Posted on 2011-02-15
11
941 Views
Last Modified: 2012-05-11
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
Comment
Question by:bruno71
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34898304

Use a LEFT JOIN
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34898332
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
 
LVL 56

Expert Comment

by:HainKurt
ID: 34898624
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:bruno71
ID: 34899055
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
 
LVL 56

Expert Comment

by:HainKurt
ID: 34899189
did you try mine? 34898624

0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34899195
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
 
LVL 56

Expert Comment

by:HainKurt
ID: 34899209
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
 

Author Comment

by:bruno71
ID: 34899356
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
 
LVL 10

Accepted Solution

by:
PSSUser earned 500 total points
ID: 34899416
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
 
LVL 56

Expert Comment

by:HainKurt
ID: 34899602
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
 

Author Comment

by:bruno71
ID: 34900115
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question