Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

difficult sql statemten

Posted on 2006-11-16
4
266 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

856 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