Comparing / deleting between tables with count(*) 1

[Azathoth]

I have two tables:

#result - has fields unit_number, rule_id
tesr - has field rule_id

In both tables, the rule_id can be duplicate. What I need to do is determine if table #result has as many occurences of the same rule_id as appear in table tesr for a particular unit number. If it does not, all rows with that rule_id should be deleted.

For example, table tesr has 3 rows in it with rule_id = 12 for unit_number 1.
Case 1: table #result also has 3 rows in it with rule_id = 12 for unit_number 1 - do nothing.
Case 2: table #result has 2 rows in it with rule_id = 12 for unit_number 1. Delete both of these rows.

I'm having trouble using count(*) and getting the syntax right...help please?

 

[JohnDTampaBay]

So both tables have unit_number as well as rule_id?

I believe this code should work. The derived tables can be created as temp tables, if you prefer. HTH, Good luck!

-- This will show you who will be deleted
SELECT r.Rule_ID, r.Unit_Number
FROM (-- Counts by rule_id and unit_number for #result
    SELECT Rule_ID, Unit_Number, Count(*) AS Cnt
    FROM #result
    GROUP BY Rule_ID, Unit_Number
  ) r
  INNER JOIN (-- Counts by rule_id and unit_number for tesr
    SELECT Rule_ID, Unit_Number, Count(*) AS Cnt
    FROM tesr
    GROUP BY Rule_ID, Unit_Number
  ) t ON t.Rule_ID = r.Rule_ID
      AND t.Unit_Number = r.Unit_Number

-- This will delete the unwanted rows
DELETE FROM #result
FROM #result r1
  INNER JOIN (-- Counts for #result
    SELECT Rule_ID, Unit_Number, Count(*) AS Cnt
    FROM #result
    GROUP BY Rule_ID, Unit_Number
  ) r ON r1.Rule_ID = r.Rule_ID
      AND r1.Unit_Number = r.Unit_Number
  INNER JOIN (-- Counts for tesr
    SELECT Rule_ID, Unit_Number, Count(*) AS Cnt
    FROM tesr
    GROUP BY Rule_ID, Unit_Number
  ) t ON t.Rule_ID = r.Rule_ID
      AND t.Unit_Number = r.Unit_Number

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Azathoth]

Unfortunately, tesr doesn't contain unit_number. unit_number can appear multiple times in #result, and have one or more rule_ids associated with it.

 

[JohnDTampaBay]

But your example states "table tesr has 3 rows in it with rule_id = 12 for unit_number 1." How do you know it has three rows for that unit_number if unit_number is not in the table?


--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Azathoth]

That was a mistake on my part. It simply has 3 rows with rule_id = 12...sorry for the confusion.

 

[JohnDTampaBay]

You can't get from here to there with the two tables given. There is simply no way in tesr to tell what unit_number any given row belongs, therefore no way to delete those rows from #result with different counts.

Is there another table you can join to tesr to acquire the unit_number?

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Azathoth]

The rows in tesr don't apply to any particular unit number: the only important thing to get from this table is the count of duplicate rule_ids. If there are three rows with rule_id = 12 in this table, I need to go to #result and tally the number of rows with duplicate unit_numbers that have rule_id = 12. If there are three (there can't be more than 3), then all the rows stay. If there are any less, they all have to go.

 

[JohnDTampaBay]

If unit_number doesn't apply, then does this do the trick?

-- This will show you who will be deleted
SELECT r.Rule_ID, r.Unit_Number
FROM (-- Counts by rule_id for #result
    SELECT Rule_ID, Count(*) AS Cnt
    FROM #result
    GROUP BY Rule_ID
  ) r
  INNER JOIN (-- Counts by rule_id for tesr
    SELECT Rule_ID, Count(*) AS Cnt
    FROM tesr
    GROUP BY Rule_ID
  ) t ON t.Rule_ID = r.Rule_ID
WHERE r.Cnt <> t.Cnt

-- This will delete the unwanted rows
DELETE FROM #result
FROM #result r1
  INNER JOIN (-- Counts for #result
    SELECT Rule_ID, Count(*) AS Cnt
    FROM #result
    GROUP BY Rule_ID
  ) r ON r1.Rule_ID = r.Rule_ID
  INNER JOIN (-- Counts for tesr
    SELECT Rule_ID, Count(*) AS Cnt
    FROM tesr
    GROUP BY Rule_ID
  ) t ON t.Rule_ID = r.Rule_ID
WHERE r.Cnt <> t.Cnt

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[ESquared]

Good work, JohnDTampaBay...

I just had to say something because I so like joining to derived tables that have a group by clause!

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[Azathoth]

Well, that solution didn't quite work, but it got my brain a little more on track...thanks for the help. Here's what I eventually came up with:

	delete from 
		#result
	where
		( select
			count( * )
		from
			#result r2
		where
			#result.unit_number = r2.unit_number
			and #result.rule_id = r2.rule_id ) <
				( select
					count( * )
				from
					tbl_eligibility_sub_rule tesr
				where
					#result.rule_id = tesr.rule_id )

It always ends up being not nearly as bad as I suspect...

 

[ESquared]

Why didn't it work? Try the following change:

DELETE [red][b]r1[/b][/red]
FROM #result r1
  INNER JOIN (-- Counts for #result
    SELECT Rule_ID, Count(*) AS Cnt
    FROM #result
    GROUP BY Rule_ID
  ) r ON r1.Rule_ID = r.Rule_ID
  INNER JOIN (-- Counts for tesr
    SELECT Rule_ID, Count(*) AS Cnt
    FROM tbl_eligibility_sub_rule tesr
    GROUP BY Rule_ID
  ) t ON t.Rule_ID = r.Rule_ID
WHERE r.Cnt <> t.Cnt

-------------------------------------
A sacrifice is harder when no one knows you've made it.