February 4, 2006 at 2:51 pm
I need help in constructing a query that will list ColA, ColB, and ColG from TableA along with the count of rows in TableB whose ColA value matches that of TableA ColA.
I like to have the option of listing either all rows in TableA regardless of whether there are any matching rows in TableB, or only rows in TableA with matching rows in TableB.
I hope that this makes some kind of sense.
Thanks,
Howard
February 4, 2006 at 7:26 pm
set nocount on
declare @tableA table (colA int, colB int, colG int)
declare @tableB table (colA int, colC varchar(10))
insert into @tableA values (1, 10, 100)
insert into @tableA values (2, 20, 200)
insert into @tableB values (1, 'value 1')
insert into @tableB values (1, 'value 2')
insert into @tableB values (1, 'value 3')
--for all rows in TableA regardless of
--matching rows in TableB
select A.colA, A.colB, A.colG, count(B.ColA) cnt
from @tableA A
left outer join @tableB B
on A.ColA = B.ColA
group by A.colA, A.colB, A.colG
--for rows in TableA with matching rows in
--in TableB
select A.colA, A.colB, A.colG, count(B.ColA) cnt
from @tableA A
inner join @tableB B
on A.ColA = B.ColA
group by A.colA, A.colB, A.colG
------------
output:
--from first query
colA colB colG cnt
----------- ----------- ----------- -----------
1 10 100 3
2 20 200 0
--from second query
colA colB colG cnt
----------- ----------- ----------- -----------
1 10 100 3
February 6, 2006 at 7:38 am
Thank you, this is exactly what I was look for.
Howard
February 6, 2006 at 7:51 am
Just for a better understanding of how things work could you modify your query below and add the clause 'where cnt > 0'
--for all rows in TableA regardless of
--matching rows in TableB
select A.colA, A.colB, A.colG, count(B.ColA) cnt
from @tableA A
left outer join @tableB B
on A.ColA = B.ColA
group by A.colA, A.colB, A.colG
Thanks
Howard
February 6, 2006 at 8:29 am
Do you want the 'where cnt > 0' to make the query to behave like the one where matches was required?
--for all rows in TableA regardless of
--matching rows in TableB
select A.colA, A.colB, A.colG, count(B.ColA) cnt
from @tableA A
left outer join @tableB B
on A.ColA = B.ColA
group by A.colA, A.colB, A.colG
HAVING count(B.ColA) > 0
colA colB colG cnt
----------- ----------- ----------- -----------
1 10 100 3
There is actually a way to use this query for both cases:
declare @cnt int
set @cnt = 0
select A.colA, A.colB, A.colG, count(B.ColA) cnt
from @tableA A
left outer join @tableB B
on A.ColA = B.ColA
group by A.colA, A.colB, A.colG
having count(B.ColA) > @cnt
colA colB colG cnt
----------- ----------- ----------- -----------
1 10 100 3
declare @cnt int
set @cnt = -1
select A.colA, A.colB, A.colG, count(B.ColA) cnt
from @tableA A
left outer join @tableB B
on A.ColA = B.ColA
group by A.colA, A.colB, A.colG
having count(B.ColA) > @cnt
colA colB colG cnt
----------- ----------- ----------- -----------
1 10 100 3
2 20 200 0
By using a variable for the counts of the HAVING clause, you can send the count as a parameter.
/Kenneth
February 6, 2006 at 8:45 am
Kenneth:
Thanks, this is the answer that I was looking for. I had forgotten about using the having clause.
If I may I would like to ask one more question concerning another query.
TableA contains columns date_of_transaction and receipt_number.
TableB contains receipt_number and a constraint on receipt_number so that each TableB entry for a receipt_number must have a matching TableA entry for the same receipt_number number. Therefore when deleting TableA and TableB transaction on or prior to a specified date TableB rows must be deleted prior deleting the matching TableA entry.
How would you construct the delete query to delete all TableA and matching TableB rows whose TableA date_of_transaction is on or prior to a specified date?
Thanks,
Howard
February 7, 2006 at 1:08 am
You'd have to do this in two steps, delete tableB first then tableA.
declare @deleteDate char(8)
set @deleteDate = '20060207' -- <=== this is intentional format to avoid any and all ambiguities
--(errorchecking omitted)
BEGIN TRAN
-- delete from tableB first
DELETE b
FROM tableB b
JOIN tableA a
on b.receipt_number = a.receipt_number
AND a.date_of_transaction <= @deleteDate
-- then tableA (this assumes that just the date is sufficient to warrant delete from tableA)
DELETE tableA
WHERE date_of_transaction <= @deleteDate
COMMIT
/Kenneth
February 7, 2006 at 2:31 pm
Kenneth:
Thank you very much for the help.
February 8, 2006 at 1:02 am
Just a final note on dates...
If your date_of_transaction column also holds time that is different from all zeroes, you need to think about how to phrase the qualification.
The above works fine for the current day and earlier, provided that all dates have midninght as their time component. Should you have hours and minutes in the time, you might need to change the criteria slightly.
(as per the example)
Set the date to tomorrow and change the operator - the net effect of the criteria < @tomorrowsDate will be that all dates today will qualify regardless of the time. (up to todaysDate 23:59:59.997)
/Kenneth
February 8, 2006 at 7:01 am
Kenneth:
Thank you for the additional information. You have been a great help to me.
Howard
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply