CODEWITHSUNDEEP
mysql
pb_SKAT
pb_SKAT

Reputation: 163

MySql find if records exist in 3 tables in one statement

I am going to delete a supplier, but before doing so I would like to make sure there are no dependent data (e.g. orders, etc) in other tables. As I have proper foreign key constraints everywhere the probably simplest approach would be to execute the delete statement on the suppliers tables and catch the error in a handler and then tell the user that deletion is not possible.

However just for interest I would like to know whether I could check the presence of dependent records in one statement that delivers one number as a result. So far I came up with

    Const chkSQL As String = _
        "SELECT COUNT(*) FROM bestellungen WHERE LiefID= @LiefID UNION " & _
        "SELECT COUNT(*) FROM lieferungen WHERE LiefID= @LiefID UNION " & _
        "SELECT COUNT(*) FROM lieferantenartikel WHERE LiefID= @LiefID"

But this will deliver 3 rows with the respective counts. If I use ExecuteScalar (where I hoped I would get the sum of the 3 values) I seem to get only the result from the last table. I have experimented with SUM but somehow I cant get the syntax right.

Any ideas?

Upvotes: 0

Views: 49

Answers (3)

John Bupit
John Bupit

Reputation: 10618

Instead of manually checking if dependent entries exist, you could setup an ON DELETE RESTRICT behavior on your foreign keys.

That way, when you try to execute a DELETE on the parent row, the query will fail if a child row exists that references the value for that parent row. If the parent row has no referencing child rows, then you can delete that parent row.

One way to add ON DELETE RESTRICT is while creating the table:

CREATE TABLE child_table (
  ...
  col_id INT,
  FOREIGN KEY fk_col (col_id) REFERENCES parent_table (col_id) ON DELETE RESTRICT
) ENGINE=InnoDB;

Upvotes: 0

Eugen
Eugen

Reputation: 805

If you execute 3 different selects's, you will always obtain 3 different results! Unless you parse and replace the responses from a programming language or something. Try something like this and instead of:

Const chkSQL As String = _
            "SELECT COUNT(*) FROM bestellungen WHERE LiefID= @LiefID UNION " & _
            "SELECT COUNT(*) FROM lieferungen WHERE LiefID= @LiefID UNION " & _
            "SELECT COUNT(*) FROM lieferantenartikel WHERE LiefID= @LiefID"

try to use somthing like this:

Const chkSQL As String = 
            "SELECT count(a.*), count(b.*), count(c.*) FROM bestellungen a, lieferungen b, lieferantenartikel c WHERE LiefID= a.@LiefID UNION and LiefID= b.@LiefID UNION and LiefID= a.@LiefID

Upvotes: 0

Praveen
Praveen

Reputation: 9345

If you want the sum of all 3 table in a row then;

select sum(cnt) as total_cnt
from (
  SELECT COUNT(*) as cnt FROM bestellungen WHERE LiefID= @LiefID UNION all
  SELECT COUNT(*) as cnt FROM lieferungen WHERE LiefID= @LiefID UNION all
  SELECT COUNT(*) as cnt FROM lieferantenartikel WHERE LiefID= @LiefID
) x

Here union all is used as union will exclude similar cnt from the result, which is not preferred.

Upvotes: 1

Related Questions