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 )







LVL 2
dMaAsked:
Who is Participating?
 
dduserConnect With a Mentor Commented:
Select Person.name,Contact.Value from Person Left Outer Join Contact on Person.Person_NR = Contact.Contact_NR and Type = 70

Regards,

dduser
0
 
TimCotteeHead of Software ServicesCommented:
Hi dMa,

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
0
 
dMaAuthor Commented:
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'
 
0
 
TimCotteeHead of Software ServicesCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.