August 25, 2009 at 6:54 pm
Hi,
I have following query: but on the last two join iam having query if
select t.testid,tl.date, ti.points 'check in', o.points 'Check Out'--,case
when (tle.id = 1 or tle.id = 2 or tle.id = 3)
then
sum(rooms)
end 'C. rooms',
case
when (tle.id = 5)
then
isnull(sum(rooms),0)
end 'out house',
isnull(sum(guestno), 0)totalcustomer
from dbo.test1 tl
INNER JOIN dbo.testdetail td ON tl.ID = td. ID AND tl. Date = td.Date
INNER JOIN dbo.test t ON td.ID = t.ID
inner join dbo.testname tn on tn.id = tl.id
inner join
(
select ts.testid, w.points
from
dbo.testts ts
INNER JOIN dbo.tablepoint w ON ts.point = w.id
where ts.type in ('Check In')
) ti on ti.testnameid = tl.id
inner join
(
select ts.testid, w.points
from
dbo.testts ts
INNER JOIN dbo.tablepoint w ON ts.point = w.id
where ts.type in ('Check out')
) o on o.testnameid = tl.id
group by t.testid,tl.date, ti.points, tle.id
if i remove last inner join then i get 4 records and if i include i get 20? because second inner join query have 5 records.
in result i only want 5 records but getting 20 what am i doing wrong?
Thanks
August 25, 2009 at 11:43 pm
Since a join shows all combinations of the joined tables in the result set, whether or not those columns are displayed, you must be getting unexpected combinations. Select DISTINCT may remove duplicates for you, but if you are still having a problem, we would need to see your data to spot where the problem lies and advise you about how to correct it.
Code without context or data doesn't give us much to work with, when trying to help you. Could you please send a script to create the tables from your query and populate them with a bit of sample data, so we can see and recreate the problem? Complete instructions and examples may be found here.[/url] Following the guidelines in the article will get you a lot more assistance, and much more quickly.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply