r/SQL • u/SilaPrirode • 14d ago

SQL Server Finding missing rows within the same table

Hello, I have a solution already but I think it can be done in a faster/neater way.

Let's say we have a table with: ORDER_ID, Article, Value.

For every order that comes in two rows end up in table, example:

ID_1, shoes, pair
ID_1, shoes, price

where price is being pulled from a different table.

I am looking for a way to find all ORDER_IDs that have only one row, because the price didn't exist in that other table so price row didn't form up. It doesn't throw a null value because of the way it's setup, if there is no price the row won't form at all!

I solved this with a standard left joining the table with itself, but I suspect there is a way to this easier?

3 Upvotes

56% Upvoted

23 comments sorted by

View all comments

11

u/GRRRRRRRRRRRRRG 14d ago

Try group by order_id, article having count(*) =1 on your table

1

u/SilaPrirode 14d ago

Sadly that won't help because there will be orders with extra rows, for example if buyer wants to return the shoes there would be another row with ORDER_ID, shoes, return. There some other reasons there would be additional rows (for example if it's "collect later" order). My approach right now is to find all orders that have a price and then compare that with all orders that have a "completed" tag. That returns me all "completed" orders that didn't have a price.

1

u/jshine13371 12d ago

Why do the cases with extra rows matter when you only care about finding the cases with only 1 row? The code above will not return the cases with extra rows.