Saujib (9/12/2013)
thank you.I want only a particular phone for the consumer.
Each consumer can have 0 phone or more than one phone number,i want only primary phone.
similarly address, consumer can have more than one address or 0 address, i want only primary address.
From consumerphone where phoneTypeID=1
From ConsumerAddress where AddressTypeID=1
For ConsumerIdentifier is a table with SSN and medicaid.
So for a particular consumer, ConsumerIdentifier table will have
ID=1 value=xxxx - where id=1 means SSN
ID=2 value=yyyy - where ID=2 means medicaid
OUTER JOIN's still apply. To limit to a particular phone type you can put "AND CP.phoneTypeID = 1" in the ON clause of the JOIN to customerPhone. To get what you want out of CustomerIdentifier you will have to JOIN to it twice with the desired TYPE in the ON CLAUSE so your query could look like this:
SELECT
C.firstName,
C.lastName,
CP.phone,
CA.ADDRESS,
SSN.value AS SSN,
MC.value AS Medicaid
FROM
dbo.consumer AS C
LEFT JOIN dbo.consumerPhone AS CP
ON C.consumerID = CP.consumerID AND
CP.phoneTypeID = 1
LEFT JOIN consumerAddress AS CA
ON C.consumerID = CA.consumerID AND
CA.addressTypeID = 1
LEFT JOIN consumerIdentifier AS SSN
ON C.consumerID = SSN.consumerID AND
SSN.identifierTypeID = 1
LEFT JOIN consumerIdentifier AS MC
ON C.consumerID = MC.consumerID AND
MC.identifierTypeID = 2;
Or you can LEFT JOIN to the "type" tables and specify a name or description in the ON clause. I'll leave that part for you to figure out.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question