How to join two tables where one table keeps rows on a separate row

[emsik1001]

Hi All

I have one table where I have:

1) part number
2) location
3) Vendor ID

and I have another table where I have additional info - list price and buy price (which sometimes is not added in error)

1) part number
2) location
3) category: list price OR buy price
4) value
-- unfortuanately I don't have Vendor ID in this table

so I have several rows for the same number and location (list price and buy price)

I need to show

1) Vendor ID
2) unique number
3) location
4) value (list price) - if added
5) value (buy price) - if added

I tried to use self join but it is not showing me all the records, I tried to use subquery but I'm not too good with subqueries ;)

Many thanks in advance
Emil

[emsik1001]

Here is what I used however it is not showing me records where UP or/and UC is missing (see below) - Any suggestions?


SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED
BEGIN TRANSACTION


SELECT e1.number, e1.location, cast(e1.cost/10000 AS DECIMAL(12,2)) AS UP , CAST(e2.cost/10000 AS DECIMAL(12,2)) AS UC


FROM TblLocation as e1 FULL OUTER JOIN TblLocation as e2 ON e1.number+e1.location = e2.number+e2.location
FULL OUTER JOIN tblMaster ON RTRIM(MasterNumber)+RTRIM(MasterLocation) = RTRIM(e2.number)+RTRIM(e2.location)

WHERE e1.category = 'UP' and e2.category = 'UC' and VendorID = 'XXX'


COMMIT TRANSACTION

[ck9663]

Hi All

I have one table where I have:

1) part number
2) location
3) Vendor ID

and I have another table where I have additional info - list price and buy price (which sometimes is not added in error)

1) part number
2) location
3) category: list price OR buy price
4) value
-- unfortuanately I don't have Vendor ID in this table

so I have several rows for the same number and location (list price and buy price)

I need to show

1) Vendor ID
2) unique number
3) location
4) value (list price) - if added
5) value (buy price) - if added

I tried to use self join but it is not showing me all the records, I tried to use subquery but I'm not too good with subqueries ;)

Many thanks in advance
Emil

Is PartNumber and Location enough for you to use as join key even without VendorID?

-- CK

[emsik1001]

combination of part number and location in the first table is unique

[ck9663]

From which table the Cost column came from?

-- CK

[emsik1001]

From the second the table --4) value--

[ck9663]

I'm a little confuse. Your desired output include these:

4) value (list price) - if added
5) value (buy price) - if added

How would you know if they are list price or buy price? Can you just post some sample data and your desired output? Include some partnumber that are not in table2 and how you want to handle it.

-- CK

[Delerna]

is something like this what you are after

SELECT PartNumber,Location,VendorID,sum(UP) as UP,sum(UC) as UC
FROM
(   SELECT a.PartNumber,a.Location,a.VendorID,
            case when b.category='UP' then Value else 0 end as UP,
            case when b.category='UC' then Value else 0 end as UC
   FROM tblMaster a
   LEFT JOIN tblLocation b on a.partnumber=b.partnumber 
                          and a.location=b.location
   WHERE VendorID='XXX'
) c
GROUP BY PartNumber,Location