CODEWITHSUNDEEP
mysql
qutron
qutron

Reputation: 1760

How to check if referenced table is not empty?

For example, I have 2 tables:

CREATE TABLE IF NOT EXISTS master(
    master_id      INT          NOT NULL AUTO_INCREMENT,
    master_name    VARCHAR(15) NOT NULL,
    PRIMARY KEY(master_id)
) TYPE=InnoDB CHARACTER SET=UTF8;

and

CREATE TABLE IF NOT EXISTS slave(
    slave_id        INT          NOT NULL AUTO_INCREMENT,
    slave_name      VARCHAR(15)  NOT NULL,
    master_id       INT
    PRIMARY KEY (slave_id), 
    FOREIGN KEY (master_id) REFERENCES master(master_id)
) TYPE=InnoDB CHARACTER SET=UTF8;

How can I check if the master table is connected to the slave table and then, if it is, I want to know if slave is empty?

Upvotes: 1

Views: 193

Answers (2)

Johan
Johan

Reputation: 76597

You can check if the two tables are connected by doing a join.

  SELECT COUNT(*) as number_of_connections
  FROM master m
  INNER JOIN slave s ON (s.master_id = m.master_id)
UNION ALL
  SELECT COUNT(*) as rows_in_slave
  FROM slave s2
UNION ALL
  SELECT COUNT(*) as rows_in_master
  FROM master m2

This query will return exactly 3 rows, that will tell you the number of connections, whether the slave table is empty and whether the master table is empty respectively.

Upvotes: 1

Jez
Jez

Reputation: 30003

I'm not sure I quite understand your question, but I'll try to answer. The master table must be 'connected' to the slave table if you created the slave table with the foreign key reference FOREIGN KEY (master_id) REFERENCES master(master_id).

As for whether the slave table is empty: it's empty if this query returns a column with zero in it:

SELECT COUNT(*) FROM slave

Upvotes: 1

Related Questions