January 11, 2008 at 3:07 pm
Hello,
I have been knocking my head around all day on this join:w00t:.
The results I am trying to return for items that have an inclusion id of 1. Easy enough said. But for those entries that do not have a 1 want to return null. However when I keep structuring the query I keep getting multiple entries. I have a feeling this is either based on the case statement or the joins themselves. Please take a look because I am obviously missing something:
select distinct v.id, v.name,inclusionid= it.id, OneLiner =(case when it.id = 1 then convert(varchar(500),i.text)
else null end)
from sow s, sow_service se, service_subservice ss, project p, subservice b ,
sow_service_lineitem l
left outer join sow_service_lineitem_note w
on l.id = w.sow_service_lineitemID,
[service] v
left outer join service_inclusion si
on v.id = si.serviceid
left outer join inclusion i
on si.inclusionid = i.id
left outer join inclusiontype it
on i.inclusiontypeid = it.id
where s.id = se.sowid
and se.serviceid = v.id
and ss.serviceid = v.id
and l.sow_serviceid = se.id
and b.id = l.subserviceid
and l.SubServiceID = ss.subserviceid
and p.id = s.projectid
--and it.type = 'One Liner'
--or si.serviceid in(2,3)
and s.id = 224
order by v.id, it.id
Thanks,
Phil
January 11, 2008 at 4:43 pm
Two things come to mind, one you should not be using left joins unless you have too. Two one of your joins has a one-to-many relationship.
January 11, 2008 at 5:03 pm
What I am trying to accomplish from the select statement is to grab v.name and i.text where the type = 1. If the type doesn't = 1 I want to return just the v.name. However since a v.name could have many i.text statements I only want to return type of 1 or if there is no type one associated with that v.name, then I wanted null to return.
January 11, 2008 at 5:33 pm
Your case statement is fine.
Is it possible for you to provide some sample data, so I can validate ithe query.
January 11, 2008 at 5:44 pm
I don't really have much to provide. The issue that I am facing is that I am only returning those records that have a type of 1, and not getting the others.
January 11, 2008 at 6:21 pm
It is tough to troubleshoot when you do not know the current output and the expected output.
I don't really have much to provide. The issue that I am facing is that I am only returning those records that have a type of 1, and not getting the others.
Does this mean that every record in the inclusionid column is returning a 1 and every Oneliner also is 1? If yes, either your joins are incorrect or your where clause is filtering out the records.
I would try creating my joins using a consistent practice. You should use all inner joins in stead of the implict inner join you are using. It makes the code easier to read and troubleshoot. Secondly, I would not use left outer join, as I mentioned earlier because this will return all records from the left side of the join regardless of a match on the right side (Note: That on large tables or complex queries this can severly impede performance). An easy way to create the joins is to let query designer make them for you and then you can adjust the query to your specification.
e.g.
select *
from table, table2
where table.col = table2.col
Should be written in the ANSI standard
select *
from table inner join table2 on table.col = table2.col
where some where clause
(note it is much easier to read a where clause in the scenario, as you do not have to rummage through all the joins.)
January 11, 2008 at 6:44 pm
Right. What I am trying to return are the v.name records that have inclusion ID of 1 and then those selected v.name records that do not have a one-liner
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply