A Self Join help please - 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)

A Self Join help please

Author

Topic

meandean
Starting Member

3 Posts

Posted - 2006-01-28 : 07:27:02

hi all at sqlteam...
please help with this problem...
Im trying to return all rows as a result, even when there is no
m2.fixture_key = 'event', i would like to have null in the result
for m2.doc_id column.
thanks for you help...

select distinct m1.event_Id, m1.Doc_id, m2.Doc_id
from map_event_fixture m1
left join map_event_fixture m2
on m1.event_id = m2.event_id
where m1.fixture_Key = 'pre'
and m2.fixture_Key = 'event'

VIG
Yak Posting Veteran

86 Posts

Posted - 2006-01-28 : 08:27:50

select distinct m1.event_Id, m1.Doc_id, m2.Doc_id
from map_event_fixture m1
left join map_event_fixture m2
on m1.event_id = m2.event_id and m2.fixture_Key = 'event'
where m1.fixture_Key = 'pre'

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-28 : 08:30:15

Its hard to understand ur Q, without table structures, some sample data and the results u expect to c.

But check whether following helps u.

select distinct m1.event_Id, m1.Doc_id, Null as [m2.Doc_id]from map_event_fixture m1 
left join map_event_fixture m2
on m1.event_id = m2.event_id
where m1.fixture_Key = 'pre'
and ( m2.fixture_Key = 'event' or m2.fixture_Key is Null or m2.fixture_Key = '' )

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-28 : 09:29:52

quote:

select distinct m1.event_Id, m1.Doc_id, m2.Doc_id
from map_event_fixture m1
left join map_event_fixture m2
on m1.event_id = m2.event_id
where m1.fixture_Key = 'pre'
and m2.fixture_Key = 'event'

Never include the criteria for *outer* tables in your WHERE clause -- it turns the join into an INNER JOIN since the NULL results are no longer included.

See this article:

http://www.sqlteam.com/item.asp?ItemID=11122

You want to put that criteria on m2 into the JOIN expresion itself:

select distinct m1.event_Id, m1.Doc_id, m2.Doc_id
from map_event_fixture m1
left join map_event_fixture m2
on m1.event_id = m2.event_id and m2.fixture_Key = 'event'
where m1.fixture_Key = 'pre'

meandean
Starting Member

3 Posts

Posted - 2006-01-28 : 14:19:28

jsmith8858, vig: those where both great, but it dont return every
event_id. I need evey event_id no mater witch fixture_key row is missing.
thanks for the help and sorry for being a pain..

Srinika thanks for your reply here's more of a sample of what i need..

event_id - fixture_key - doc_id
1 - pre - xt.3753972
1 - event - xt.3758227
1 - post - xt.3758294
1 - stats - xt.3758687
2 - event - xt.3758999
2 - post - xt.3758998
2 - stats - xt.3758997
3 - pre - xt.3739484

result

event_id - m1.doc_id - m2.doc_id - m3.doc_id - m4.doc_id
1 - xt.3753972 - xt.3758227 - xt.3758294 - xt.3758294
2 - NULL - xt.3758999 - xt.3758998 - xt.3758997
3 - xt.3739484 - Null - Null - Null

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-28 : 19:57:46

Looks like what you need is row to columns

select	event_id,
	max(case when fixture_key = 'pre' then doc_id else null end) as m1,
	max(case when fixture_key = 'event' then doc_id else null end) as m2,
	max(case when fixture_key = 'post' then doc_id else null end) as m3,
	max(case when fixture_key = 'stats' then doc_id else null end) as m4
from	events
group by event_id

----------------------------------
'KH'
I come I saw I post

meandean
Starting Member

3 Posts

Posted - 2006-01-28 : 23:00:33

khtan thanks
thats the one!!!!
Its like the old crosstab of access..
thanks again
meandean

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-29 : 04:47:35

also refer to cross-tab / pivot table in the sticky FAQ http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210

----------------------------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources