need advise on sql statement - 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)

need advise on sql statement

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-04-11 : 01:02:14

Hi friends
i need advise on following
i have 2 tables (say parentTab,childTab)
Childtab has 2 foreign keys bound to same parentTable.
the fields are called fk_fld1,fk_fld2

what i want to do is write a single sql statement that'll do something like below

if fk_fld1 is null then return fk_fld2
if fk_fld2 is null then return fk_fld1

i know i can write if statement and with two separate sql statements but am just wondering if it possible with 1 sql statement.
Thanks for ur help.

Cheers

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-11 : 01:38:06

Somthing like this

Select Case When fk_fld1 is null Then fk_fld2
Case When fk_fld2 is null Then fk_fld1 End As Fk From ChildTab

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-04-11 : 01:42:16

Thanks for that. i did not phrase correctly i dont want to display FK fields but some parent Table fields.

i mean something like
if fk_fld1 is not null then select childTable.flds,parenttable.flds where parenttable.id=fk_fld1
if fk_fld2 is not null then select childTable.flds,parenttable.flds where parenttable.id=fk_fld2
hope it makes sense now.
Thanks

Cheers

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-11 : 02:10:21

Somthing like this


Declare @Childtable TAble 
(
fk_fld1 int,
fk_fld2 int
)

Declare @ParentTable Table 
(
pk int
)

Insert @ParentTable 
Select 1 union all select 2 union all select 3 union all select 4 

Insert @childTable 
Select 1,null 
union all 
Select null,2 
union all 
Select null,3
union all 
Select 4,null



Select * From @ParentTable,@ChildTable Where 
(fk_fld1 is null or fk_fld1 = pk) And 
(fk_fld2 is null or fk_fld2 = pk)

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

rmason
Starting Member

12 Posts

Posted - 2006-04-11 : 07:44:59

Is this what you mean??

Create Table #tmpP
(
tmpID Int
)

Insert #tmpP
Select 1 Union
Select 2 Union
Select 3 Union
Select 4 Union
Select 5

Select *
From #tmpP

Create Table #tmpC
(
tmpID1 Int,
tmpID2 Int
)

Insert #tmpC
(
tmpID1
)
Select 1 Union
Select 3 Union
Select 5

Insert #tmpC
(
tmpID2
)
Select 2 Union
Select 4

Select *
From #tmpC

Select *
From #tmpP p
Join #tmpC c
On p.tmpID = Coalesce(c.tmpID1, c.tmpID2)

Drop Table #tmpP
Drop Table #tmpC

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-04-11 : 16:56:25

Thank you very much. thats what am after.
Chirag
your solution also worked only if i change "AND" to "OR" i mean
Select * From @ParentTable,@ChildTable Where
(fk_fld1 is null or fk_fld1 = pk) OR
(fk_fld2 is null or fk_fld2 = pk)
I'll have to go with "rmason"'s solution as thats what am after.as my first preference is flfld1 so if we both fields are filled in i want details of fkfld1.
and rmason solution does exactly that for me.
Thank you both again :)

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-04-11 : 16:58:13

hi one more question
what if both FK fields are empty i still want other details of child table. how can i change abt query please ?
Thanks

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources