So easy a caveman can do it, but I can't

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)

So easy a caveman can do it, but I can't

Author

Topic

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-16 : 12:31:26

I'm hitting a bland here

The first SQL
select reference from MyTable where id = 6776 and term = 3
select reference from MyTable where id = 6776 and term = 5
select reference from MyTable where id = 6776 and term = 7
select reference from MyTable where id = 6776 and term = 10

Will return 4 result windows...the first one empty because there is no reference for term = 3 and next 3 have results
1.
2. 44
3. 59
4. 74

Now I try to combine them like this
select reference from MyTable where entityid = 6776
and term in (3,5,7,10)

1. 44
2. 59
3. 74

I have to get 4 rows back, with the first row empty as when i do 4 separate queries.
I know it's not that complex but my basic SQL escapes me.

Thanks.

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-16 : 12:51:38

something like this maybe...
select *
from
(select 3 as term union all select 5 union all select 7 union all select 10) as terms
left join
mytable
on mytable.term = terms.term
and mytable.id = 6776

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-16 : 13:10:59

Wow, I learn something new today....

Thanks very much,anonymous1

daman
Yak Posting Veteran

72 Posts

Posted - 2007-11-20 : 14:17:31

Another question:
How would I select from a list of preferred sources, if there nothing from that sources, then select whatever available

id value sourceid
10 15 A
10 17 B
10 15 C
10 15 Others
12 14 Something else
12 14.5 B
12 15.5 C
12 16 Something else

So i would select all record with id =10,11,12 and select source A first, if there is no source A, then B, then C and so on. My output would be....

id value sourceid
10 15 A
12 14 Something else
12 14.5 B

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-11-20 : 14:33:17

quote:
Originally posted by daman

I have to get 4 rows back, with the first row empty as when i do 4 separate queries.

Do you a table of terms, where term is the primary key or at least a unique index? Because you should really do this using left outer joins rather than hard-coding values into UNION statements.

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources