Combine data from 2 tables with case statement

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 2008 Forums

Transact-SQL (2008)

Combine data from 2 tables with case statement

Author

Topic

flipfyre
Starting Member

26 Posts

Posted - 2011-09-14 : 12:04:44

I have a problem in which I seem to be missing a key part. I have 2 tables, both with a column called equipment_uid.

Table 1 (called equipment) contains a description of all the equipment:

equipment_uid | equipment_desc
1 | trucks
2 | buses
3 | cars
4 | trains
5 | scooters
6 | bicycles
7 | skateboards

Table 2 (called location) contains the the equipment and the locations

location | equipment_uid
KY | 1
KY | 3
KY | 4
KY | 7
TN | 2
TN | 3
TN | 6
FL | 1
FL | 2
FL | 4

Therefore, KY has trucks, cars, trains, skateboards.

I need to combine the equipment and location table with a new column (called available) that will display a Yes or No if the equipment is available. I also want to limit this for KY. Like so:

equipment_desc | location | available
trucks | KY | Yes
buses | KY | No
cars | KY | Yes
trains | KY | Yes
scooters | KY | No
bicycles | KY | No
skateboards | KY | Yes

I've tried the following code:

select e.equipment_desc, l.location, available = case when (e.equipment_uid = l.equipment_uid) then ‘Yes’ else ‘No’ end
from equipment e, location l
where location = ‘KY’

but it is looping through every item in the equipment table like so:

equipment_desc | location | available
trucks | KY | Yes
buses | KY | No
cars | KY | No
trains | KY | No
scooters | KY | No
bicycles | KY | No
skateboards | KY | No

trucks | KY | No
buses | KY | No
cars | KY | Yes
trains | KY | No
scooters | KY | No
bicycles | KY | No
skateboards | KY | No

trucks | KY | No
buses | KY | No
cars | KY | No
trains | KY | Yes
scooters | KY | No
bicycles | KY | No
skateboards | KY | No

trucks | KY | No
buses | KY | No
cars | KY | No
trains | KY | No
scooters | KY | No
bicycles | KY | No
skateboards | KY | Yes

I've tried using left joins, but it doesn't work right. A little help please?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-09-14 : 12:17:40

select e.equipment_desc, l.location
,[available] = case when l.equipment_uid is not null then 'Yes' else 'No' end
from equipment e
left join location l
on e.equipment_uid = l.equipment_uid
where location = 'KY'

Jim

Everyday I learn something that somebody else already knew

flipfyre
Starting Member

26 Posts

Posted - 2011-09-14 : 14:38:09

Jim,

Thanks for the post. My database actually doesn't have any null values for the l.equipment_uid column. It was designed so that it only posts the equipment a location has.

One of the left joins I did try is similiar to yours, with the case statement like so:

select e.equipment_desc, l.location
,available = case when (e.equipment_uid = l.equipment_uid) then 'Yes' else 'No' end
from equipment e
left outer join location l on e.equipment_uid = l.equipment_uid
where location = 'KY'

However, it only produces a partial return of the data that is a match between the 2 tables, like so:

equipment_desc | location | available
trucks | KY | Yes
cars | KY | Yes
trains | KY | Yes
skateboards | KY | Yes

It needs to look like this:

equipment_desc | location | available
trucks | KY | Yes
buses | KY | No
cars | KY | Yes
trains | KY | Yes
scooters | KY | No
bicycles | KY | No
skateboards | KY | Yes

More opinions/thoughts are much appreciated.

flipfyre
Starting Member

26 Posts

Posted - 2011-09-15 : 14:05:47

Any other ideas guys?

flipfyre
Starting Member

26 Posts

Posted - 2011-09-15 : 17:06:56

Figured it out guys.

The problem was with the 'where' clause in the statement.

select e.equipment_desc, l.location
,available = case when (e.equipment_uid = l.equipment_uid) then 'Yes' else 'No' end
from equipment e
left outer join location l on e.equipment_uid = l.equipment_uid
where location = 'KY'

Apparently, having the 'where' clause at the end of a join forces the join into an inner join - thereby limiting the data. Replacing the 'where' with an 'and' produces anticipated results.

select e.equipment_desc, l.location
,available = case when (e.equipment_uid = l.equipment_uid) then 'Yes' else 'No' end
from equipment e
left outer join location l on e.equipment_uid = l.equipment_uid
and location = 'KY'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources