sql null fk - 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 2005 Forums

Transact-SQL (2005)

sql null fk

Author

Topic

jamesdwhite
Starting Member

9 Posts

Posted - 2007-02-07 : 07:13:34

Hi everyone

i have a table which contains some columns that can contain null values but are also FK to other tables

i need to select all the rows and columns from the first table and it not null some of the columns from the fk tables

is this possible thank you

james

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 07:15:25

Do you have some DDL that describes your tables?
And some sample data would be great!

Peter Larsson
Helsingborg, Sweden

jamesdwhite
Starting Member

9 Posts

Posted - 2007-02-07 : 07:44:06

table definitions (simplified)
table 1:

name initDocs
columns:
id (int) (PK)
priorityId (FK) (int)<-----
notes (string) ¦
¦
table 2: ¦
name: Prioritys ¦
coulumns: ¦
id (int) (PK) <------------
value (string)

hope this helps a little

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-07 : 07:51:10

Okay...now how about some sample data?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-07 : 07:52:44

And the result that you want

jamesdwhite
Starting Member

9 Posts

Posted - 2007-02-07 : 08:19:39

sample data is fairly simple

table 1
idInit________priorityId______notes
------------------------------------------
1___________1_____________example 1
2___________2_____________example 2
3 2 example 3
4___________NULL______example 4

table 2

idPrio________valuePrio
----------------------
1 This is very important
2 This is not so important

results should be somthing like
idInit priorityId notes ___ valuePrio
---------------------------------
1_________1______example 1____This is very important
2_________2______example 2____This is not so important
3_________2______example 3____This is not so important
4_________NULL___example 4____NULL

i hope this comes out ok :)

hope it helps

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-07 : 08:23:57

[code]
select i.idInit, i.priorityId, i.notes, p.valuePrio
from initDocs i left join Prioritys p
on i.priorityId = p.idPrio
[/code]

jamesdwhite
Starting Member

9 Posts

Posted - 2007-02-07 : 08:41:26

Thanks for the reply now supose i have another column in table 1

table 1
idInit________priorityId______notes______statusId<----new column
------------------------------------------
1___________1_____________example 1_______1
2___________2_____________example 2_______2
3___________2_____________example 3_______NULL
4___________NULL_______example 4__________NULL

And i need to do the same as before with this coulumn as well is it possible to do it with the FROM part as well ?

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-07 : 08:58:23

[code]
select i.idInit, i.priorityId, i.notes, p.valuePrio, i.statusid
from initDocs i left join Prioritys p
on i.priorityId = p.idPrio
[/code]

jamesdwhite
Starting Member

9 Posts

Posted - 2007-02-07 : 10:21:57

sorry i ment if the new column was from a third table :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-07 : 10:43:50

[code]
select i.idInit, i.priorityId, i.notes, p.valuePrio, t.statusid
from initDocs i left join Prioritys p
on i.priorityId = p.idPrio
inner join thirdtable t
on somecol = t.somecol
[/code]

jamesdwhite
Starting Member

9 Posts

Posted - 2007-02-08 : 14:05:23

thats excellent thank you :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources