Solved

difficult sql statemten

Posted on 2006-11-16
4
278 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
[X]
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
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

690 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