if, heelp needed - 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

SQL Server 2000 Forums

Transact-SQL (2000)

if, heelp needed

Author

Topic

patbe
Starting Member

9 Posts

Posted - 2004-03-20 : 08:35:40

think I'm way out of line here, think you understand what I'm trying to do, don't get the if's to work, any suggestions?

SELECT *
FROM person
IF (SELECT Distributor_ID FROM person WHERE Person_ID = 1) <> NULL
BEGIN
INNER JOIN distributor ON person.Distributor_ID = distributor.Distributor_ID
END
IF (SELECT Producer_ID FROM person WHERE Person_ID = 1) <> NULL
BEGIN
INNER JOIN producer ON person.Producer_ID = distributor.Producer_ID
END
WHERE Person_ID = 1

thx
.p

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2004-03-20 : 11:22:05

Is this what you're trying to do?

select *
from person a
inner join distributor b on a.distributor_ID = b.distributor_ID
inner join producer c on a.producer_ID = b.producer_ID
where a.person_id = 1

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-20 : 11:42:26

Valter, I guess he wants to join to only one of the tables, the one in which the person has a record. Your query will return a result only if there is an entry for this person id in BOTH the tables. patbe, is this correct? In this case, you can do something like this:


SELECT * FROM
 Person p LEFT JOIN Distributor d
 ON p.PersonID = d.PersonID
 LEFT JOIN Producer c
 ON p.PersonID = c.PersonID
 WHERE p.PersonID = 1

This will return nulls for the columns from the Distributor table if there is no corresponding record for this person in it, same goes for the Producer table. So depending on your data, you might get some rows with data from both tables or neither of them.

OS

patbe
Starting Member

9 Posts

Posted - 2004-03-22 : 02:43:24

mohd. you're so right, and after modifying your query little it's works just fine, thx alot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources