0

I new to SQL but I have been practicing JOIN a bit. I want to check if all the records from another table(small_table) has been included in my current table(base_table).

For example:

SELECT * FROM base_table WHERE year = 2015 AND month = 3 AND day = 4

will return to me a set of records based on todays date. small_table is the same structure. I want to return the count of a INNER JOIN (compared on all columns) and see if it equals the number of records in small_table. This will prove that every row/record from small_table is included in base_table based on todays date.

I don't need to use JOIN but it is simply the first method I thought of. I am open to other methods. I am having trouble creating this query.

A = SELECT * FROM small_table;
B = SELECT * FROM base_table WHERE year = 2015 AND month = 3 AND day = 4;

SELECT COUNT(*) FROM A INNER JOIN B;
Liondancer
  • 15,721
  • 51
  • 149
  • 255

1 Answers1

1

There are a few ways I think you can do this. Assuming you don't want to see all the records individually, I think COUNT is the way to go. If you want to use a JOIN, you need to join the two tables on some kind of identifier.

SELECT COUNT(s.*)
FROM small_table s
INNER JOIN base_table b
ON s.ID = b.ID
WHERE b.year = 2015 AND b.month = 3 AND b.day = 4;

The ID above is whatever your primary key would be.

Alternatively, you can use a UNION statement, which I think is the better approach.

SELECT 'Small Table' as Table_Name, COUNT(*)
FROM small_table
UNION
SELECT 'Base Table', COUNT(*)
FROM base_table
WHERE year = 2015 AND month = 3 AND day = 4;

I expect this would show something like this:

Table_Name | COUNT(*)

Small Table | 200

Base Table | 200

Where 200 is the count of each table.

Hope this helps!

bbrumm
  • 1,342
  • 1
  • 8
  • 13