By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,607 Members | 1,982 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,607 IT Pros & Developers. It's quick & easy.

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

P: 93
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
Mar 5 '08 #1
Share this Question
Share on Google+
7 Replies


P: 93
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
Mar 5 '08 #2

ck9663
Expert 2.5K+
P: 2,878
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
Mar 5 '08 #3

P: 93
combination of part number and location in the first table is unique
Mar 5 '08 #4

ck9663
Expert 2.5K+
P: 2,878
From which table the Cost column came from?

-- CK
Mar 5 '08 #5

P: 93
From the second the table --4) value--
Mar 6 '08 #6

ck9663
Expert 2.5K+
P: 2,878
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
Mar 6 '08 #7

Delerna
Expert 100+
P: 1,134
is something like this what you are after
Expand|Select|Wrap|Line Numbers
  1. SELECT PartNumber,Location,VendorID,sum(UP) as UP,sum(UC) as UC
  2. FROM
  3. (   SELECT a.PartNumber,a.Location,a.VendorID,
  4.             case when b.category='UP' then Value else 0 end as UP,
  5.             case when b.category='UC' then Value else 0 end as UC
  6.    FROM tblMaster a
  7.    LEFT JOIN tblLocation b on a.partnumber=b.partnumber 
  8.                           and a.location=b.location
  9.    WHERE VendorID='XXX'
  10. ) c
  11. GROUP BY PartNumber,Location
  12.  
Mar 7 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.