Very complex query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Very complex query

Author

Topic

Jelmer850i
Starting Member

1 Post

Posted - 2007-05-14 : 08:24:37

I have a table like this:

Customers:

CustomerID
CustomerName
person1
Person2

Persons:

PersonID
PersonName
PersonAdress

No can person2 be empty !!!! But person1 is almost everytime filled.
I like to get a query with this fields:

CustomerName Persons.PersonName (of Person1) Persons.PersonName (of Person2, only if this field is filled)

Is that possible, and how?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-14 : 08:28:13

use LEFT JOIN


select c.CustomerName, p1.PersonName, p2.Personname
from   Customers c
left join Persons p1
       on c.person1 = p1.personid
left join Persons p2
       on c.person2 = p2.personid

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 08:28:42

Do you have a constraint on person2?

select c.customerid, coalesce(p1.personname, p2.personname) AS personname
from customers as c
left join persons as p1 on p1.personid = c.person1
left join persons as p2 on p2.personid = c.person2

select c.customerid, coalesce(p2.personname, p1.personname) AS personname
from customers as c
left join persons as p1 on p1.personid = c.person1
left join persons as p2 on p2.personid = c.person2

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 08:29:08

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-05-14 : 08:29:34


SELECT CustomerName, P1.PersonName, P2.PersonName
FROM Customers AS C
    LEFT OUTER JOIN Persons AS P1
        ON P1.PersonID = C.person1
    LEFT OUTER JOIN Persons AS P2
        ON P2.PersonID = C.Person2

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources