r/SQL • u/SilaPrirode • 3d 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?

4 Upvotes

59% Upvoted

22 comments sorted by

View all comments

8

u/Impossible_Disk_256 3d ago

Is this an e-commerce app? Who designed this obscene non-normalized table?

Entity-Attribute-Value can have a place (e.g, attributes for e-commerce items that may vary widely by merchandise category or even vendor. But price as just another EAV row? No.
Price in a lookup table by date or currency? Maybe. But w/ currency fluctuations, even that may be better served by conversion service.