October 10, 2003 at 9:52 am
Hi-
I have a situation where the following is the table structure.
tblVendor:
----------
VendorId INT IDENTITY
DataId INT (FK REFERENCES tblData.DataId)
tblCustomer:
----------
CustomerId INT IDENTITY
DataId INT (FK REFERENCES tblData.DataId)
tblData:
--------
DataId INT IDENTITY
DataName VARCHAR(100)
DataCategory VARCHAR(50)
DataCategory tells me if the record is a Vendor or a Customer. We have some very strong reasons why we had to design it this way. Now my task is to display the
DataId and the Associated Id in the Vendor or Customer tables depending on the Category.
I can't figure this out. I know we need to use a CASE but can I use CASE to join the tables? I was trying something in the lines of:
SELECT DISTINCT tbldata.DataID,
CASE tblDataCategory.DataCategoryName
WHEN 'Customer' THEN tblCustomer.CustomerId
WHEN 'Vendor' THEN tblVendor.VendorId
END InstanceId
FROM tblData AS tbldata,
INNER JOIN tblDataCategory,
ON tblData.DataCategoryId = tblDataCategory.DataCategoryId
CASE tblDataCategory.DataCategoryName
WHEN 'Customer' THEN tblCustomer.CustomerId
WHEN 'Vendor' THEN Vendor.VendorId
END InstanceId
Appreciate all your help
Sarat
October 10, 2003 at 12:10 pm
Sarat
Is this what you're looking for? Just paste my code into QA replacing my table vars with your tables.
Hope this helps.
-kel
begin
declare @tblVendor table (VendorId INT IDENTITY,DataId INT )
declare @tblCustomer table (CustomerId INT IDENTITY, DataId INT)
declare @tblData table (DataId INT IDENTITY,DataName VARCHAR(100),DataCategory VARCHAR(50) )
insert into @tblData (DataName, DataCategory)
values('data item1','Customer')
insert into @tblData
values('data item2','Vendor')
insert into @tblCustomer(DataId)
values(1)
insert into @tblVendor(DataId)
values(2)
SELECT DISTINCT (D1.DataId) as dataID,
CustomerId as linkedID,
D1.DataName as dataname,
'Customer' as linktype
from @tblData D1
inner join @tblCustomer C on C.DataId = D1.DataId
Union
SELECT DISTINCT (D.DataID) as dataID,
VendorID as linkedID,
D.DataName as dataname,
'Vendor' as linktype
from @tblData D inner join @tblVendor V on V.DataID = D.DataID
end
K Leb
October 10, 2003 at 5:54 pm
You might try the following... Since I don't have a schema to try it on it may fail. But it should give you the idea.
SELECT
td.DataID
, CASE td.DataCategory
WHEN 'Customer' THEN (SELECT DISTINCT CustomerID FROM tblCustomer WHERE DataID = td.DataID)
WHEN 'Vendor' THEN (SELECT DISTINCT VendorID FROM tblVendor WHERE DataID = td.DataID)
ELSE NULL
END InstanceID
FROM tblData td
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
October 10, 2003 at 6:12 pm
Working from your pseudo DDL rather than the schema in your pseudo SQL, perhaps you want just this:
SELECT d.DataId,
CASE d.DataCategory
WHEN 'Vendor' THEN v.VendorId
WHEN 'Customer' THEN c.CustomerId
END AS InstanceId
FROM tblData d
LEFT JOIN tblVendor v ON d.DataId = v.DataId
LEFT JOIN tblCustomer c ON d.DataId = c.DataId
Using DISTINCT makes no sense, as the rsults are guaranteed unique by the IDENTITY property (and I assume they are the primary keys).
--Jonathan
--Jonathan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply