fk problem - 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

SQL Server Development (2000)

fk problem

Author

Topic

allansuperze
Starting Member

4 Posts

Posted - 2005-10-26 : 07:37:03

Hello guys, I'm in need of help here

I have 3 tables, lets call then, t1, t2 and t3.
t1 is my main table, and throu t2 i want to reach t3.

the tables are as folow
t1
t1ID1
t1ID2
lot of other fields(loof)

t2
t1ID1
t3ID
some other fields(sof)

t3
t3ID
t1ID2
field I want(fiw)

my query

select * from t1
LEFT JOIN t2 on (t1.t1ID1 = t2.t1ID1)
LEFT JOIN t3 on (t2.t3ID = t2.t3ID)
where t1.t1ID2 = t3.t1ID2

ok, that is good, but not always I will find a record in t2 that does the link or the where clause will eliminate the result, in both cases there is a defaul value for the "fiw"

so I came with the query

select t1.loof, t3.fiw from t1
LEFT JOIN t2 on (t1.t1ID1 = t2.t1ID1)
LEFT JOIN t3 on (t2.t3ID = t2.t3ID)
where t1.t1ID2 = t3.t1ID2
UNION
SELECT t1.loof, 'default value'
FROM t1
WHERE t1ID1 NOT IN (select t1ID1 from t1
LEFT JOIN t2 on (t1.t1ID1 = t2.t1ID1)
LEFT JOIN t3 on (t2.t3ID = t2.t3ID)
where t1.t1ID2 = t3.t1ID2)

it's ok, works just fine, but I don't like the performance, i want to know if there is a simpler and more optimized way to do that

thanks for any help

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-10-26 : 20:36:45

Does t1.t1ID2 always = t3.t1ID2?
Also, it is better if you post your data with real table/column info; it will help to see the data that is/isn't returning right, and then we can duplicate your situation.

Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Kristen
Test

22859 Posts

Posted - 2005-10-26 : 23:56:48

Is this what you need


SELECT t1.loof, 
       CASE WHEN t2.t3ID IS NULL THEN 'default value' ELSE t3.fiw END AS [fiw]
FROM t1 
    LEFT JOIN t2 
        ON t1.t1ID1 = t2.t1ID1
    LEFT JOIN t3 
        ON t2.t3ID = t2.t3ID
       AND t1.t1ID2 = t3.t1ID2

This will select duplicate values of t1 where corresponding t2 exist but matching t2 do not exist - which may not be what you want, in which case you will need some brackets so that you LEFT JOIN T2and INNER JOIN T3 to that Left Join. That will still give you all t1 that have no t3

Kristen

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-10-27 : 07:41:07

That's what I was thinking, Kristen. I just couldn't get my head around it. Funny how once you posted your code I looked and said "Duh!"

Pretty obvious that t1.t1ID2 = t3.t1ID2!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Kristen
Test

22859 Posts

Posted - 2005-10-27 : 14:17:19

I'll send you a cardboard cutout of myself - you will find that by just explaining the problem to my cardboard-self the solution will become apparent! Guaranteed or your money back!!!

Kristen

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-10-27 : 16:36:25

I love it!
I just looked at a different post I did last night, really screwed it up without typing a single line of code! I will make a cardboard cutout of my paycheck and send it to you for your cardboard likeness cutout. Guaranteed to have the same value as the cardboard it's printed on or your money back!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources