I have table A with column appleId.
I have table B with column bappleId.
Creating a row in table A should create a row in table B where appleId = bappleId.
Table A
| Id | appleId |
|---|---|
| 3 | 33 |
| 4 | 34 |
| 5 | 35 |
| 6 | 36 |
Table B Data
| Id | bappleId |
|---|---|
| 1 | 33 |
| 2 | 34 |
How can I query to make sure rows in table B were created? For example, I have 4 rows in table A and only 3 in table B means 2 rows are missing, but I also want the appleId, not just the count.
Expected output:
Something along the lines of 35, 36, which are the missing rows’ appleId/bappleId in table B or even better would be 5, 6 since thats the id of the table A rows which did not get created in table B
Doing something like
select bappleId
from table b as b
where not exists (select appleid from table a as a where a.appleId = b.bappleid)
returns only the extra rows in table B, not the missing ones in table A
anon3289hfe9w is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
You can use LEFT Join and a NULL condition
SELECT a.Id, a.appleId
FROM TableA a
LEFT JOIN TableB b ON a.appleId = b.bappleId
WHERE b.bappleId IS NULL;
Output
Using the Minus operator, the database will do the work for you.
select appleid as appleid from tablea
minus
select bappleid as appleid from tableb;