difficult sql statemten
hi experts,
i have 2 tables one is master secound client 1-N th ( client table have many entries that are pointed to one entry in the master) and also have an type field.
now what i need is a sql select statment thats gives me
master structure: person table
person_NR - PK
name - varchar
detail structure: contact table
contact_NR - PK
person_NR - FK
value - varchar
type - int
sql result:
1. gives me ALL person.name and the contact.value thats have the contact.type 70 and all the rest of master thats have a different contract.type as 70 but if the more as 1 result should be only one time
what i wanna have is just somthing like select * from person (any entries one time) and also the value that have the type 70 or if they dont null must be in.
result:
name value
person 1 , hallo ( have the type 70 and it's hallo )
person 2 , hallo ( have the type 70 and it's hallo )
person 3 , hallo ( have the type 70 and it's hallo )
person 4 , null ( donot have the type 70 result = null )
i have 2 tables one is master secound client 1-N th ( client table have many entries that are pointed to one entry in the master) and also have an type field.
now what i need is a sql select statment thats gives me
master structure: person table
person_NR - PK
name - varchar
detail structure: contact table
contact_NR - PK
person_NR - FK
value - varchar
type - int
sql result:
1. gives me ALL person.name and the contact.value thats have the contact.type 70 and all the rest of master thats have a different contract.type as 70 but if the more as 1 result should be only one time
what i wanna have is just somthing like select * from person (any entries one time) and also the value that have the type 70 or if they dont null must be in.
result:
name value
person 1 , hallo ( have the type 70 and it's hallo )
person 2 , hallo ( have the type 70 and it's hallo )
person 3 , hallo ( have the type 70 and it's hallo )
person 4 , null ( donot have the type 70 result = null )
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
so hi timcotte thats my sql text now
SELECT Person.name, CASE WHEN contakt.art_nr = 70 THEN contakt.value ELSE 'Unknown' END AS [Value]
FROM contakt INNER JOIN
person ON contakt.person_NR = person.person_NR
the problem is a get severals results for one person
i need for
in case of art_nr= 70 just one result from master and in case of art_nr <> 70 also just one result from master with the state of null or 'uknown'
SELECT Person.name, CASE WHEN contakt.art_nr = 70 THEN contakt.value ELSE 'Unknown' END AS [Value]
FROM contakt INNER JOIN
person ON contakt.person_NR = person.person_NR
the problem is a get severals results for one person
i need for
in case of art_nr= 70 just one result from master and in case of art_nr <> 70 also just one result from master with the state of null or 'uknown'
dduser,
SELECT Distinct Person.name, CASE WHEN contakt.art_nr = 70 THEN contakt.value ELSE 'Unknown' END AS [Value]
FROM contakt INNER JOIN
person ON contakt.person_NR = person.person_NR
Adding the distinct will give you that as there can only be at most one combination of person + art_nr=70 and one combination of person + null. Therefore you will get at most two records for each person.
Tim
SELECT Distinct Person.name, CASE WHEN contakt.art_nr = 70 THEN contakt.value ELSE 'Unknown' END AS [Value]
FROM contakt INNER JOIN
person ON contakt.person_NR = person.person_NR
Adding the distinct will give you that as there can only be at most one combination of person + art_nr=70 and one combination of person + null. Therefore you will get at most two records for each person.
Tim
Select Master.Person As [Name], Case When Detail.[Type]=70 Then Detail.[Value] Else Null End As [Value] From Detail Inner Join Master On Detail.person_NR = Master.person_NR
Tim Cottee