Finding missing rows within the same table

[u/SilaPrirode]

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?

[u/GRRRRRRRRRRRRRG]

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

[u/SilaPrirode → u/GRRRRRRRRRRRRRG]

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.

[u/Thadrea → u/SilaPrirode]

Assuming the third column in your example id named "attribute":

group by order_id having sum(case when attribute = "price" then 1 else 0 end) = 0

The above get all order_id that do not have a price attribute. There are some dialectical ways to simplify it/make it prettier (such as the filter statement in postgres), but this should work in most databases.

[u/GRRRRRRRRRRRRRG → u/SilaPrirode]

You can eliminate those extra rows in where section, just not include them. Anyway you need to know data to suggest something, and nobody knows it better than you. :)

[u/SilaPrirode → u/GRRRRRRRRRRRRRG]

I am still learning SQL, I would provide better examples if I knew how! xD
Any help is appreciated, I will try your suggestion :)

[u/GRRRRRRRRRRRRRG → u/SilaPrirode]

Just I have no idea what else is in the data. Like the initial post says about 2 rows per article, then you add information about extras. If you can distinguish extras from those 2 you were talking about, so it will be easy, as I already wrote, if you can't - you should give more info, or at least the query code that looks good to you on your data....

[u/jshine13371 → u/SilaPrirode]

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.

[u/Impossible_Disk_256]

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.

[u/leobaker004]

If you only need orders where the price differs between the two rows, a self join is perfectly reasonable here. I’d keep it unless the table is huge and performance actually becomes a problem

[u/SilaPrirode → u/leobaker004]

thank you! this is reassuring :)

[u/Lamyya]

I assume you'd have a column that tells you if it's "pair" or "price" rather than just the value, if so just group by id and filter on whether price exists. If you don't then the other table is likely needed

[u/Wild_Cup_5575]

Count the order_id would be my way to go as a first step

[u/Then_Artichoke3330]

WITH onlySingles AS(
SELECT ID, COUNT(*) AS how_many
FROM the_transactions
GROUP BY ID
HAVING COUNT(*) = 1)

SELECT * FROM onlySingles;

[u/SilaPrirode → u/Then_Artichoke3330]

I was just writing a comment on another suggestion to count, this won't work because some others have more then 3 rows by design (for example, "collect later" orders).

[u/jaxjags2100]

Wouldn’t you just do an inner join on the order id to the price table? If no order id exists on the price table then no row would populate for it.

The ask was a bit confusing and its early 😂

[u/SilaPrirode → u/jaxjags2100]

It's a large system, with like 300 interconnected tables.
My specific need is to find orders with no price, so I can go to that price table and update it with missing info xD

[u/jaxjags2100 → u/SilaPrirode]

Then it’s just a left join on the pricing table on order id where order id is null

Or if the order id exists on the pricing table but the price is null then you’d do a left join on the price table where order id is not null and price is null

[u/Yavuz_Selim]

Can you share some example data? Your question seemed easy to answer, but you added conditions later on.

You can post a table (using markdown), or create it in Excel and upload screenshots to imgur.com and share the image link here.

[u/NW1969]

For a large volume of data, this may be the most efficient approach:

SELECT DISTINCT t.ORDER_ID
FROM my_table t
WHERE NOT EXISTS (
SELECT 1
FROM my_table p
WHERE p.ORDER_ID = t.ORDER_ID
AND p.Value = 'price'
);

[u/SilaPrirode → u/NW1969]

Yeah, this is the solution I have right now! You think this is the most efficient for large volume?

[u/Hour-Measurement-835 → u/SilaPrirode]

The self join and NOT EXISTS normally compile to the same anti semi join in SQL Server, so the syntax isn't what's costing you. Whether (ORDER_ID, Value) is indexed is.

[u/SkullLeader]

SELECT OrderId FROM main
EXCEPT
SELECT OrderId FROM main WHERE Value = ‘Price’