December 14, 2007 at 11:38 am
i have a person table and a product table
i need to identify if a person, has a number of products and if so set a feild to 'Y' and if not to 'N'
its a one to many relationship
in other words
Person table
person name
person id
Product table will have
person id
product name
product date aquired
i need to look in the product table, join on personid and for each from product in the table
set
hasprod1 =y
hasprod2 =y
hasprod3 =n
etc
[font="Comic Sans MS"][/font]It is what it is.
December 14, 2007 at 3:41 pm
CREATE TABLE #Persons (
PersonID int IDENTITY(1,1) primary key,
PersonName varchar(20)
)
INSERT#Persons
SELECT 'Bob' UNION
SELECT 'Sally' UNION
SELECT 'Lara'
CREATE TABLE #Products (
ProductID int IDENTITY(1,1) primary key,
ProductName varchar(20),
PersonID int,
ProductDateAcquired smalldatetime
)
INSERT#Products
SELECT 'Fork', 1, GETDATE() UNION
SELECT 'Spoon', 1, GETDATE()UNION
SELECT 'Fork', 3, GETDATE()
SELECTPersonName, COALESCE(ProductName, ''),
CASE
WHEN ProductName IS NULL THEN 'N'
ELSE 'Y'
END 'HasProduct'
FROM#Persons pr
LEFT OUTER JOIN #Products ps
ON pr.PersonID = ps.PersonID
DROP TABLE #Persons
DROP TABLE #Products
December 17, 2007 at 9:34 am
i see. i'll try it.
thanks
[font="Comic Sans MS"][/font]It is what it is.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply