Solved

difficult sql statemten

Posted on 2006-11-16
4
250 Views
Last Modified: 2008-07-03
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 )







0
Comment
Question by:dMa
  • 2
4 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 17955632
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
 
LVL 9

Accepted Solution

by:
dduser earned 125 total points
ID: 17955675
Select Person.name,Contact.Value from Person Left Outer Join Contact on Person.Person_NR = Contact.Contact_NR and Type = 70

Regards,

dduser
0
 
LVL 2

Author Comment

by:dMa
ID: 17955948
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
 
LVL 43

Expert Comment

by:TimCottee
ID: 17956004
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now