Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

difficult sql statemten

Posted on 2006-11-16
4
Medium Priority
?
284 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 500 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

715 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