found record base on count records on child table

I

inungh

I tried to get records from parent table which does not have child in
the children table and have following SQL statement.

I got syntax error in the having clause.

"having (select count(*) from tbl|ActivityReasonReview where
[tblActivityReason].[ReasonId] = [tblActivityReasonReview].[ReasonID]
= 0) "

am I missing something here?
Your help is great appreciated,




SELECT tblActivityReason.ReasonID, tblActivityReason.Employee_ID,
tblEmployee.First_Name+" "+tblEmployee.Last_Name AS Employee,
tblActivityReason.ActivityDate, tblActivityReason.ActivityCode,
TblActivetyCode.Activity_description
FROM ( tblActivityReason INNER JOIN TblActivetyCode ON
TblActivetyCode.Activity = tblActivityReason.ActivityCode) INNER JOIN
tblEmployee ON tblActivityReason.Employee_ID = tblEmployee.Employee_ID
WHERE ((tblActivityReason.Employee_ID)=[forms]![fservicelevel]!
[cmbEmployeeName])
having (select count(*) from tbl|ActivityReasonReview where
[tblActivityReason].[ReasonId] = [tblActivityReasonReview].[ReasonID]
= 0)
ORDER BY tblActivityReason.ActivityDate DESC;
 
B

Bob Barrows [MVP]

inungh said:
I tried to get records from parent table which does not have child in
the children table
Click to expand...
This is a job for an outer join.

select p.* from
parenttable as p left outer join childtable c
on p.keyfields = c.keyfields
where c.keyfieldname is null
and have following SQL statement.

I got syntax error in the having clause.

"having (select count(*) from tbl|ActivityReasonReview where
[tblActivityReason].[ReasonId] = [tblActivityReasonReview].[ReasonID]
= 0) "

am I missing something here?
Click to expand...

Yes.
1. You can only use a HAVING clause if your query contains a GROUP BY
clause.
2. The =0 part should be outside the parentheses surrounding the
subquery

There are two ways to accomplish this. The first is with a subquery,
similar to your approach:

SELECT tblActivityReason.ReasonID, tblActivityReason.Employee_ID,
tblEmployee.First_Name+" "+tblEmployee.Last_Name AS Employee,
tblActivityReason.ActivityDate, tblActivityReason.ActivityCode,
TblActivetyCode.Activity_description
FROM ( tblActivityReason INNER JOIN TblActivetyCode ON
TblActivetyCode.Activity = tblActivityReason.ActivityCode) INNER JOIN
tblEmployee ON tblActivityReason.Employee_ID = tblEmployee.Employee_ID
WHERE ((tblActivityReason.Employee_ID)=[forms]![fservicelevel]!
[cmbEmployeeName])
and not exists (select * from tbl|ActivityReasonReview where
[tblActivityReason].[ReasonId] = [tblActivityReasonReview].[ReasonID])
ORDER BY tblActivityReason.ActivityDate DESC;

The second is with a left join:

SELECT tblActivityReason.ReasonID, tblActivityReason.Employee_ID,
tblEmployee.First_Name+" "+tblEmployee.Last_Name AS Employee,
tblActivityReason.ActivityDate, tblActivityReason.ActivityCode,
TblActivetyCode.Activity_description
FROM (( tblActivityReason INNER JOIN TblActivetyCode ON
TblActivetyCode.Activity = tblActivityReason.ActivityCode) INNER JOIN
tblEmployee ON tblActivityReason.Employee_ID
= tblEmployee.Employee_ID)
WHERE ((tblActivityReason.Employee_ID)=[forms]![fservicelevel]!
[cmbEmployeeName]) left join tbl|ActivityReasonReview
on [tblActivityReason].[ReasonId] = [tblActivityReasonReview].[ReasonID]
and [tblActivityReasonReview].[ReasonID] is Null
ORDER BY tblActivityReason.ActivityDate DESC;
 
I

inungh

inungh said:
I tried to get records from parent table which does not have child in
the children table
Click to expand...

This is a job for an outer join.

select p.* from
parenttable as p left outer join childtable c
on p.keyfields = c.keyfields
where c.keyfieldname is null
and have following SQL statement.
Click to expand...
I got syntax error in the having clause.
Click to expand...
"having (select count(*) from tbl|ActivityReasonReview where
[tblActivityReason].[ReasonId] = [tblActivityReasonReview].[ReasonID]
= 0) "
Click to expand...
am I missing something here?
Click to expand...

Yes.
1. You can only use a HAVING clause if your query contains a GROUP BY
clause.
2. The =0 part should be outside the parentheses surrounding the
subquery

There are two ways to accomplish this. The first is with a subquery,
similar to your approach:

SELECT tblActivityReason.ReasonID, tblActivityReason.Employee_ID,
tblEmployee.First_Name+" "+tblEmployee.Last_Name AS Employee,
tblActivityReason.ActivityDate, tblActivityReason.ActivityCode,
TblActivetyCode.Activity_description
FROM ( tblActivityReason INNER JOIN TblActivetyCode ON
TblActivetyCode.Activity = tblActivityReason.ActivityCode) INNER JOIN
tblEmployee ON tblActivityReason.Employee_ID = tblEmployee.Employee_ID
WHERE ((tblActivityReason.Employee_ID)=[forms]![fservicelevel]!
[cmbEmployeeName])
and not exists (select * from tbl|ActivityReasonReview where
[tblActivityReason].[ReasonId] = [tblActivityReasonReview].[ReasonID])
ORDER BY tblActivityReason.ActivityDate DESC;

The second is with a left join:

SELECT tblActivityReason.ReasonID, tblActivityReason.Employee_ID,
tblEmployee.First_Name+" "+tblEmployee.Last_Name AS Employee,
tblActivityReason.ActivityDate, tblActivityReason.ActivityCode,
TblActivetyCode.Activity_description
FROM (( tblActivityReason INNER JOIN TblActivetyCode ON
TblActivetyCode.Activity = tblActivityReason.ActivityCode) INNER JOIN
tblEmployee ON tblActivityReason.Employee_ID
= tblEmployee.Employee_ID)
WHERE ((tblActivityReason.Employee_ID)=[forms]![fservicelevel]!
[cmbEmployeeName]) left join tbl|ActivityReasonReview
on [tblActivityReason].[ReasonId] = [tblActivityReasonReview].[ReasonID]
and [tblActivityReasonReview].[ReasonID] is Null
ORDER BY tblActivityReason.ActivityDate DESC;

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...

Thanks for helping,
Should outer join clause before where clause?
I tried sub query method, it returns the records which child table
exists records when I use exists clause.

It returns all records if I use not exists.

If give me syntax error when I tried to use left outer join.

Am I missing someting here?
Thanks again,
 
B

Bob Barrows [MVP]

inungh said:
inungh said:
I tried to get records from parent table which does not have child
in the children table
Click to expand...

This is a job for an outer join.

select p.* from
parenttable as p left outer join childtable c
on p.keyfields = c.keyfields
where c.keyfieldname is null
and have following SQL statement.
Click to expand...
I got syntax error in the having clause.
Click to expand...
"having (select count(*) from tbl|ActivityReasonReview where
[tblActivityReason].[ReasonId] =
[tblActivityReasonReview].[ReasonID] = 0) "
Click to expand...
am I missing something here?
Click to expand...

Yes.
1. You can only use a HAVING clause if your query contains a GROUP BY
clause.
2. The =0 part should be outside the parentheses surrounding the
subquery

There are two ways to accomplish this. The first is with a subquery,
similar to your approach:

SELECT tblActivityReason.ReasonID, tblActivityReason.Employee_ID,
tblEmployee.First_Name+" "+tblEmployee.Last_Name AS Employee,
tblActivityReason.ActivityDate, tblActivityReason.ActivityCode,
TblActivetyCode.Activity_description
FROM ( tblActivityReason INNER JOIN TblActivetyCode ON
TblActivetyCode.Activity = tblActivityReason.ActivityCode) INNER JOIN
tblEmployee ON tblActivityReason.Employee_ID =
tblEmployee.Employee_ID WHERE
((tblActivityReason.Employee_ID)=[forms]![fservicelevel]!
[cmbEmployeeName])
and not exists (select * from tbl|ActivityReasonReview where
[tblActivityReason].[ReasonId] =
[tblActivityReasonReview].[ReasonID]) ORDER BY
tblActivityReason.ActivityDate DESC;

The second is with a left join:

SELECT tblActivityReason.ReasonID, tblActivityReason.Employee_ID,
tblEmployee.First_Name+" "+tblEmployee.Last_Name AS Employee,
tblActivityReason.ActivityDate, tblActivityReason.ActivityCode,
TblActivetyCode.Activity_description
FROM (( tblActivityReason INNER JOIN TblActivetyCode ON
TblActivetyCode.Activity = tblActivityReason.ActivityCode) INNER JOIN
tblEmployee ON tblActivityReason.Employee_ID
= tblEmployee.Employee_ID)
WHERE ((tblActivityReason.Employee_ID)=[forms]![fservicelevel]!
[cmbEmployeeName]) left join tbl|ActivityReasonReview
on [tblActivityReason].[ReasonId] =
[tblActivityReasonReview].[ReasonID] and
[tblActivityReasonReview].[ReasonID] is Null
ORDER BY tblActivityReason.ActivityDate DESC;
Click to expand...

Thanks for helping,
Should outer join clause before where clause?
Click to expand...

Yes. Ughh! How did I mangle it like that?? It should be:

SELECT tblActivityReason.ReasonID, tblActivityReason.Employee_ID,
tblEmployee.First_Name+" "+tblEmployee.Last_Name AS Employee,
tblActivityReason.ActivityDate, tblActivityReason.ActivityCode,
TblActivetyCode.Activity_description
FROM (( tblActivityReason INNER JOIN TblActivetyCode ON
TblActivetyCode.Activity = tblActivityReason.ActivityCode) INNER JOIN
tblEmployee ON tblActivityReason.Employee_ID
= tblEmployee.Employee_ID) left join tbl|ActivityReasonReview
on [tblActivityReason].[ReasonId] = [tblActivityReasonReview].[ReasonID]

WHERE ((tblActivityReason.Employee_ID)=[forms]![fservicelevel]!
[cmbEmployeeName])
and [tblActivityReasonReview].[ReasonID] is Null
ORDER BY tblActivityReason.ActivityDate DESC;

I tried sub query method, it returns the records which child table
exists records when I use exists clause.

It returns all records if I use not exists.
Click to expand...

Very strange. I don't have access to the data so I can't see what's
going on.
If give me syntax error when I tried to use left outer join.
Click to expand...

see above
 
I

inungh

inunghwrote:
inunghwrote:
I tried to get records from parent table which does not have child
in the children table
This is a job for an outer join.
select p.* from
parenttable as p left outer join childtable c
on p.keyfields = c.keyfields
where c.keyfieldname is null
and have following SQL statement.
I got syntax error in the having clause.
"having (select count(*) from tbl|ActivityReasonReview where
[tblActivityReason].[ReasonId] =
[tblActivityReasonReview].[ReasonID] = 0) "
am I missing something here?
Yes.
1. You can only use a HAVING clause if your query contains a GROUP BY
clause.
2. The =0 part should be outside the parentheses surrounding the
subquery
There are two ways to accomplish this. The first is with a subquery,
similar to your approach:
SELECT tblActivityReason.ReasonID, tblActivityReason.Employee_ID,
tblEmployee.First_Name+" "+tblEmployee.Last_Name AS Employee,
tblActivityReason.ActivityDate, tblActivityReason.ActivityCode,
TblActivetyCode.Activity_description
FROM ( tblActivityReason INNER JOIN TblActivetyCode ON
TblActivetyCode.Activity = tblActivityReason.ActivityCode) INNER JOIN
tblEmployee ON tblActivityReason.Employee_ID =
tblEmployee.Employee_ID WHERE
((tblActivityReason.Employee_ID)=[forms]![fservicelevel]!
[cmbEmployeeName])
and not exists (select * from tbl|ActivityReasonReview where
[tblActivityReason].[ReasonId] =
[tblActivityReasonReview].[ReasonID]) ORDER BY
tblActivityReason.ActivityDate DESC;
The second is with a left join:
SELECT tblActivityReason.ReasonID, tblActivityReason.Employee_ID,
tblEmployee.First_Name+" "+tblEmployee.Last_Name AS Employee,
tblActivityReason.ActivityDate, tblActivityReason.ActivityCode,
TblActivetyCode.Activity_description
FROM (( tblActivityReason INNER JOIN TblActivetyCode ON
TblActivetyCode.Activity = tblActivityReason.ActivityCode) INNER JOIN
tblEmployee ON tblActivityReason.Employee_ID
= tblEmployee.Employee_ID)
WHERE ((tblActivityReason.Employee_ID)=[forms]![fservicelevel]!
[cmbEmployeeName]) left join tbl|ActivityReasonReview
on [tblActivityReason].[ReasonId] =
[tblActivityReasonReview].[ReasonID] and
[tblActivityReasonReview].[ReasonID] is Null
ORDER BY tblActivityReason.ActivityDate DESC;
Click to expand...
Click to expand...
Thanks for helping,
Should outer join clause before where clause?
Click to expand...

Yes. Ughh! How did I mangle it like that?? It should be:

SELECT tblActivityReason.ReasonID, tblActivityReason.Employee_ID,
tblEmployee.First_Name+" "+tblEmployee.Last_Name AS Employee,
tblActivityReason.ActivityDate, tblActivityReason.ActivityCode,
TblActivetyCode.Activity_description
FROM (( tblActivityReason INNER JOIN TblActivetyCode ON
TblActivetyCode.Activity = tblActivityReason.ActivityCode) INNER JOIN
tblEmployee ON tblActivityReason.Employee_ID
= tblEmployee.Employee_ID) left join tbl|ActivityReasonReview
on [tblActivityReason].[ReasonId] = [tblActivityReasonReview].[ReasonID]

WHERE ((tblActivityReason.Employee_ID)=[forms]![fservicelevel]!
[cmbEmployeeName])
and [tblActivityReasonReview].[ReasonID] is Null
ORDER BY tblActivityReason.ActivityDate DESC;
I tried sub query method, it returns the records which child table
exists records when I use exists clause.
Click to expand...
It returns all records if I use not exists.
Click to expand...

Very strange. I don't have access to the data so I can't see what's
going on.


If give me syntax error when I tried to use left outer join.
Click to expand...

see above

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks for helping,
It is working now,
Thanks again,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

basConcatenate on New records 2
Delete categories in a table based on a group query 3
How do I link a new record to a record on the child table? 2
How to refer to a control on a child form in the on close event of the master fo 3

Top