select query - join - 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)

select query - join

Author

Topic

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-28 : 09:46:19

i have select query....

select distinct duo.messageid_ from [detected unique opens] duo

left outer join (select MailingID, count(*) as cnt
from lyrCompletedRecips
where mailingid = duo.messageid_
and FinalAttempt is not null
AND FinalAttempt >= '1945-09-10 00:00:00'
group by MailingID) ad
on ad.mailingid = duo.messageid_

i m getting error like:

The column prefix 'duo' does not match with a table name or alias name used in the query.

can anyone tell me what's the reason?
thanks.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-28 : 09:51:12

Can you post sample data and table structure and tell us what you are trying to achieve?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-28 : 10:06:18

quote:
Originally posted by ri16

i have select query....

select distinct duo.messageid_ from [detected unique opens] duo

left outer join (select MailingID, count(*) as cnt
from lyrCompletedRecips
where mailingid = duo.messageid_
and FinalAttempt is not null
AND FinalAttempt >= '1945-09-10 00:00:00'
group by MailingID) ad
on ad.mailingid = duo.messageid_

i m getting error like:

The column prefix 'duo' does not match with a table name or alias name used in the query.

can anyone tell me what's the reason?
thanks.

remove the condition in red from where clause and try

ri16
Yak Posting Veteran

64 Posts

Posted - 2008-02-28 : 10:15:32

thanks visakh16 but i m getting incorrect results..

i m using this select query in function. which is like this:

create function [dbo].[mystatslist](@list_ varchar(255))
RETURNS TABLE
AS
RETURN (
select distinct duo.messageid_, dv.title_, duo.list_, duo.[detected unique opens] as [Unique Opens],
dto.[detected total opens] as [Total opens], uc.[unique clickthroughs] as [Unique Clickthroughs],
tc.[total clickthroughts] as [Total Clickthroughts] , ad.cnt as [Completed Delivery],
cd.cnt as [Atempted Delivery], ar.cnt as [Active Recipients]

from [detected unique opens] duo

left outer join mystattitlelistvw dv
on duo.messageid_ = dv.messageid_

left outer join [detected total opens] dto
on duo.list_ = dto.list_ and duo.messageid_ = dto.messageid_

left outer join [unique clickthroughs] uc
on duo.list_ = uc.list_ and duo.messageid_ = uc.messageid_

left outer join [total clickthroughts] tc
on duo.list_ = tc.list_ and duo.messageid_ = tc.messageid_

left outer join (select MailingID, count(*) as cnt
from lyrCompletedRecips
where ---mailingid = duo.messageid_ and
FinalAttempt is not null
AND FinalAttempt >= '1945-09-10 00:00:00'
group by MailingID) ad
on duo.messageid_ = ad.mailingid

left outer join (select mailingID, count(*) as Cnt
from lyrCompletedRecips
where --mailingid = duo.messageid_ and
FinalAttempt is not null
AND FinalAttempt >= '1945-09-10 00:00:00'
and completionstatusid = 300
group by MailingID, completionstatusid ) cd
on duo.messageid_ = cd.mailingid

left outer join (select MailingID, count(*) as cnt
from lyrActiveRecips
---where mailingid = duo.messageid_
group by MailingID ) ar
on duo.messageid_ = ar.mailingid

where duo.list_ = @list_

)

here i m getting correct results except last column ar.cnt..whcih i m getting mull values if i m not using where clause..and also some rows r not showing without using where clause which is incorrect results...

thanks for ur help..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources