Query help with outer joins - 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)

Query help with outer joins

Author

Topic

nelfia
Starting Member

2 Posts

Posted - 2008-11-07 : 12:20:52

Hey there. I am hoping some of you like looking at queries and figuring out how to formulate them or solve problems with them... because I have one and am hoping someone can help me! Thanks in advance. :) (And yes I am using SQL Server 2005)

----

I am having trouble with a SQL query that involves one or more left outer joins. Here is a (simplified as much as possible) description of the query:

ORIGINAL QUERY
--------------
* Two tables involved - EventCode and ConfigEventCode
* EventCode contains EventCodeId(unique/primary) and CustomerCode, Code, and Description
* ConfigEventCode contains 2 foreign keys, EventCodeId, ConfigurationId and two other columns which are unused here.

select distinct(EventCode.EventCodeId) as EventCodeId,
EventCode.Code as Code,
EventCode.Description as Description,
EventCode.CustomerCode as CustCode,
case
when ConfigEventCode.EventCodeId is null then 'false'
else 'true'
end
as 'Used in a Config'

from EventCode
left outer join ConfigEventCode on
EventCode.EventCodeId = ConfigEventCode.EventCodeId
where EventCode.CustomerCode = @CustomerCode
order by EventCodeId

------

So the point of this query is to retrieve a general list of unique event codes (no dups) for the right customer code, and the last column will show as true if there is any event code with a corresponding configuration entry. The above is a working query but I have to make a modification to it.

Now I have to bring another table, Configuration into the query. As you can probably guess this is a table with primary/unique of ConfigurationId and there are two columns I am retrieving out of it, both boolean. For simplicity's sake I will call them ColOne and ColTwo. they are type bit.

The change in the query has to be as follows:

* Add two columns to the result to indicate true/false for the associated configuration. Column names will be something like 'Has a ColOne' and 'Has a ColTwo'. If there is no Configuration row then it's false.

----

I was thinking that I need to do another left outer join to achieve this, so I tried doing this:

to the end of the SELECT part of the statement I added:

case
when Configuration.ColOne = '1' then 'true'
else 'false'
end
as 'Has ColOne',
case
when Configuration.ColTwo = '1' then 'true'
else 'false'
end
as 'Has ColTwo'

-- and then the rest of the query looked like --

from EventCode
left outer join ConfigEventCode on
EventCode.EventCodeId = ConfigEventCode.EventCodeId
left outer join Configuration on
Configuration.ConfigurationId = ConfigEventCode.ConfigurationId

WHERE EventCode.CustomerCode = @CustomerCode
order by EventCodeId

The problem with this is it gives me multiple event code rows returned and I need only one. Note that an event can (and usually does) have multiple configurations associated with it. Here is the crux of the problem, since I am looking to see if ANY Configuration has a true value in one or both of the columns. Is this too complex to fit into one query or is it doable in some way?

(The reason I am trying to fit it into one query is not because I'm masochistic but because I would rather avoid having to do two queries if possible... however if it is necessary, I can manage with it somehow)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-07 : 12:30:40

[code]SELECT ec.EventCodeId,
ec.Code,
ec,Description,
ec.CustomerCode,
CASE WHEN t.EventCodeId IS NULL
then 'false'
else 'true'
end
as 'Used in a Config',
CASE WHEN t.Col1 IS NULL
then 'false'
else 'true'
end
as 'Has a Col One',
CASE WHEN t.Col2 IS NULL
then 'false'
else 'true'
end
as 'Has a Col Two'
FROM EventCode ec
left outer join
(SELECT EventCodeId,
SUM(CASE WHEN ColOne=1 THEN 1 ELSE 0 END) AS Col1,
SUM(CASE WHEN ColTwo=1 THEN 1 ELSE 0 END) AS Col2
FROM ConfigEventCode cec
INNER JOIN Configuration c
on c.ConfigurationId=cec.ConfigurationId
GROUP BY EventCodeId)t
on ec.EventCodeId = t.EventCodeId
where ec.CustomerCode = @CustomerCode
order by ec.EventCodeId[/code]

nelfia
Starting Member

2 Posts

Posted - 2008-11-07 : 13:01:07

Wow, thanks for the fast reply, I appreciate it !

Thanks very much!! I will analyze and study this query to learn from it but clearly it works perfect when I run it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources