need help with SQL statement

[shaminda]

I have two tables say tableA an tableB.

TableA
fdUserLoadId PartNumber Qty CompFlag fdHoldSerial
1 78500 A400 5 C 49
2 78500 A400 3 C 49
3 78500 A500 2 C 49
4 78500 A300 1 C 49
5 78500 A200 5 C 49



TableB
fdItemId PartNumber Stn1Qty HoldFlag fdHoldSerial
12 78500 A400 1 N 49
13 78500 A400 2 N 49
14 78500 A500 1 N 49
15 78500 A300 1 N 49
16 78500 A200 4 N 49

I want to write a select statement that will give me the following result. If you notice the fdHoldSerial number on both tables are the same. How do I do this?


fdUserLoadId PartNumber Stn1Qty HoldFlag fdHoldSerial
1 78500 A400 5 N 49
2 78500 A400 3 N 49
3 78500 A500 2 N 49
4 78500 A300 1 N 49
5 78500 A200 5 N 49

 

[AlexCuse]

Maybe this (not tested, or even parsed)

select distinct 
a.fdUserLoadId, a.PartNumber, a.Qty, b.HoldFlag, a.fdHoldSerial
from TableA a inner join TableB b 
on a.fdHoldSerial = b.fdHoldSerial
and a.PartNumber = b.PartNumber

Or something along those lines. This is assuming that you really want a.Qty to be displayed (these are values you typed) rather than b.Stn1Qty (which is what you indicated in column header).

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[rac2]

I dont see any column that allows you to match up a row from Table A with the corresponding row in Table B.

What is the relationship between the two tables? One-to-one, one-to-many, or many-to-many? What column or columns can be used to tie them together?

 

[shaminda]

rac2,
fdHoldSerial on both tables are the same.

AlexCuse,
I tried this query. If the PartNumber is the same on all the records the query will return 25 records. It returns more than 5 records.

 

[AlexCuse]

fdHoldSerial is not enough to give you a one to one match when joining the two tables, apparently not even when using distinct and part number as well. Your problem really appears to be one of table design.

Ignorance of certain subjects is a great part of wisdom

 

[Catadmin]

If you don't want all the PartNumbers, try adding that to your ON statement to filter out the ones that aren't the same.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"