May 11, 2004 at 4:53 am
The following query is a simple query, where I have introduced an extra "counting" column, which I labelled x.
However, I dont seem to be able to use x in my WHERE clause.
This is surprising.
Is there a solution where I dont have to repeat the count criterian in the where clause? Thanks.
select vcFname,
(select count(*) from tbl_ProjectsResearchers PR
where
PR.fk_intprojectID = pk_intProjectID
) as x
from tbl_Projects
where x > 0
May 11, 2004 at 6:46 am
How about
select P.vcFname, count(*) as x
from tbl_Projects P
inner join tbl_ProjectsResearchers PR
on PR.fk_intprojectID = P.pk_intProjectID
group by P.vcFname
Far away is close at hand in the images of elsewhere.
Anon.
May 12, 2004 at 2:44 am
Syntax Error, Not surprising 😉
One way:
select vcFname, x.cnt
from
( select fk_intprojectID, count(*) as cnt
from tbl_ProjectsResearchers PR
group by fk_intprojectID ) as x
join tbl_Projects on x.fk_intprojectID = pk_intProjectID
where x.cnt > 0
Also the x > 0 implies that you want to retrieve the vcFname for projects that have researchers, correct ?
If so:
select vcFname from tbl_Projects where exists(
select * from tbl_ProjectsResearchers
where fk_intprojectID = pk_intProjectID )
/rockmoose
You must unlearn what You have learnt
May 12, 2004 at 7:38 am
SELECT pj.vcFname, dt.noIDs
FROM tbl_Projects pj
JOIN
(SELECT fk_intprojectID,
COUNT(*) AS noIDs
FROM tbl_ProjectsResearchers
GROUP BY fk_intprojectID) dt
ON dt.fk_intprojectID = pj.pk_intProjectID
WHERE dt.noIDs > 0
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply