October 15, 2009 at 8:58 pm
Hye all..Im new here.. i wrote a query but unfortunately it cannot execute..I try to change the error but it failed..I dont know what to do now..This is my query..
SELECT distinct p.programid, p.ProgramName + ' ( ' + CAST(Duration AS varchar(5)) + ' years ' + ')' as programname,p.collegeid,
et.target,c.shortname,l.description,cmp.shortname as campus,p.levelid,p.programid,p.duration,i.intakecode,
(SELECT COUNT(*) AS Expr1 FROM ApplicantOnline ao WHERE ao.finalized =1 AND ao.Completed = 1 AND ao.IntakeID = 23
and ao.choice1=p.programid) AS Apply,
(SELECT COUNT(*) FROM applicant a WHERE (Offer = 1) AND
(IntakeID = 23) AND p.programid=a.programid) AS Offered,
(select count (*) from applicant a4 inner join applicantpayment ap4 on a4.nricno = ap4.nricno
inner join race as r4 on r4.raceid = a4.race
inner join racecategory as rc4 on rc4.racecategoryid = r4.racecategoryid
where ap4.finalized=1 and a4.intakeid = 23 and
a4.programid = p.programid and ap4.paymenttypeid=2 and ap4.intakeid = 23) as Accept,
(select count(*) as numstudents from student s
where s.studentstatus is null and s.program=p.programid
and s.NRICNo in
(select n.nricno from newapplicant n Where n.nricno = s.nricno and n.intakeid=23 group by p.programid,n.nricno) )as Register
FROM applicant a,program p
join intake i on i.intakeid=a.intakeid
join campus cmp on p.campusid=cmp.campusid
join college c on c.collegeid=p.collegeid
join level l on l.levelid=p.levelid
join enrollmenttarget et on et.programid=p.programid
group by p.programname,p.collegeid,c.shortname,p.levelid,l.description,cmp.shortname,p.programid,p.duration,p.maxbest5,et.target,a.ProgramID
order by cmp.shortname desc,p.collegeid,p.levelid desc
the bold code is where the error occurs..And this is the error while executing:
Msg 4104, Level 16, State 1, Line 23
The multi-part identifier "a.intakeid" could not be bound.
October 16, 2009 at 1:00 am
Does the table applicant have a column named intakeid?
p.s. do you realise you have a cross join here?
FROM applicant a,program p
That will return a Cartesian product of the rows in applicant and the rows in program. Each row in applicant will be 'joined' to all the rows in program. If say applicant had 10 rows and program had 5, this would return 50. Is that what you want?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2009 at 1:37 am
GilaMonster (10/16/2009)
Does the table applicant have a column named intakeid?p.s. do you realise you have a cross join here?
FROM applicant a,program p
That will return a Cartesian product of the rows in applicant and the rows in program. Each row in applicant will be 'joined' to all the rows in program. If say applicant had 10 rows and program had 5, this would return 50. Is that what you want?
yup..applicant have intakeid column..yup..thats what i want..but how can i solve my problem..?
in program table it didnt contain intakeid..im stucked for about 2 weeks on this..
October 16, 2009 at 1:43 am
The issue is here...
FROM applicant a,program p
join intake i on i.intakeid=a.intakeid
How is applicant joined to program ?
FROM applicant a
join intake i on i.intakeid=a.intakeid,
program p
This will work , but as Gail said you have a cartesian join between applicant and program.
October 16, 2009 at 1:58 am
Dave Ballantyne (10/16/2009)
The issue is here...
FROM applicant a,program pjoin intake i on i.intakeid=a.intakeid
How is applicant joined to program ?
FROM applicant ajoin intake i on i.intakeid=a.intakeid,
program p
This will work , but as Gail said you have a cartesian join between applicant and program.
ooohh..now i understand..i've try the query above..it works but it shows duplicate data..
October 16, 2009 at 2:04 am
alifah (10/16/2009)
it works but it shows duplicate data..
Most probably because of cartesian join.
October 16, 2009 at 2:12 am
Dave Ballantyne (10/16/2009)
alifah (10/16/2009)
it works but it shows duplicate data..
Most probably because of cartesian join.
hmmm..im so new wif sql..can anybody help me how to reduce the error..
October 16, 2009 at 2:16 am
alifah (10/16/2009)
hmmm..im so new wif sql..can anybody help me how to reduce the error..
Dont think im being awkward for the sake of it but ...
Look at the tables, look at the data, look at the relationships between the tables, and add in the appropriate join condition.
October 16, 2009 at 4:09 am
What's the relationship between applicant and program? Eiher a direct relationship or one via other tables?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 18, 2009 at 6:12 pm
its a direct relationship with foreign key programid..
October 19, 2009 at 1:29 am
Can you show us how you have tried to resolve the issue ?
October 19, 2009 at 1:55 am
alifah (10/18/2009)
its a direct relationship with foreign key programid..
Then you should have a join between the two tables on that column, just as you've joined applicant and intake
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply