Nested SQL Queries with Self JOIN - How to filter rows OUT

Question

I have an SQLite3 database with a table upon which I need to filter by several factors. Once such factor is to filter our rows based on the content of other rows within the same table.

From what I've researched, a self JOIN is going to be required, but I am not sure how I would do that to filter the table by several factors.

Here is a sample table of the data:

Name Part # Status Amount --------------------------------- Item 1 12345 New $100.00 Item 2 12345 New $15.00 Item 3 35864 Old $132.56 Item 4 12345 Old $15.00

What I need to do is find any Items that have the same Part #, one of them has an "Old" Status and the Amount is the same.

So, first we would get all rows with Part # "12345," and then check if any of the rows have an "Old" status with a matching Amount. In this example, we would have Item2 and Item4 as a result.

What now would need to be done is to return the REST of the rows within the table, that have a "New" Status, essentially discarding those two items.

Desired Output:

Name Part # Status Amount --------------------------------- Item 1 12345 New $100.00

Removed all "Old" status rows and any "New" that had a matching "Part #" and "Amount" with an "Old" status. (I'm sorry, I know that's very confusing, hence my need for help).

I have looked into the following resources to try and figure this out on my own, but there are so many levels that I am getting confused.

The first two links dealt with comparing columns within the same table. The third one does seem to be a pretty similar question, but does not have a readable answer (for me, anyway).

I do Java development as well and it would be fairly simple to do this there, but I am hoping for a single SQL query (nested), if possible.

Can you provide a sample expected output to clarify what you are desiring to occur? — yanman1234
– yanman1234, Commented Jul 19, 2017 at 19:44
In your example would you want item 3? It has no "new" record but it does have an Old one. You seem to indicate not with "Removed all "OLD" status rows and any new that matched..." @p1erstef has a good response IMO it's definitely the occam razor's answer :P — xQbert
– xQbert, Commented Jul 19, 2017 at 20:14
Put another way... think you want to see all the new parts which didn't have a matching price and part number for an old name. — xQbert
– xQbert, Commented Jul 19, 2017 at 20:21

p1erstef · Accepted Answer · 2017-07-20 07:27:40Z

3

The "not exists" statment should do the trick :

select * from table t1 where t1.Status = 'New' and not exists (select * from table t2 where t2.Status = 'Old' and t2.Part = t1.Part and t2.Amount = t1.Amount);

edited Jul 20, 2017 at 7:27

answered Jul 19, 2017 at 19:53

p1erstef

3944 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

xQbert Over a year ago

Seems to be most efficient though select * could be select 1 or a column name. * seems like overkill in memory/performance; but then again, maybe the engines are smart enough to ignore the select since nothing is used from it.

CL. Over a year ago

@xQbert Databases indeed ignore columns in EXISTS; it checks for rows, not for column values. (That's why this is the only place where SELECT * is actually a good idea.)

KeithL · Accepted Answer · 2017-07-19 19:50:59Z

This is a T-SQL answer. Hope it is translatable. If you have a big data set for matches you might change the not in to !Exists.

select * from table where Name not in( select Name from table t1 join table t2 on t1.PartNumber = t2.PartNumber AND t1.Status='New' AND t2.Status='Old' and t1.Amount=t2.Amount) and Status = 'New'

ScaisEdge · Accepted Answer · 2017-07-19 19:53:32Z

could be using an innner join a grouped select for get status old and not only this

select * from my_table INNER JOIN ( select Part_# , Amount , count(distinct Status) , sum(case when Status = 'Old' then 1 else 0 ) from my_table group part_#, Amount, having count(distinct Status)>1 and sum(case when Status = 'Old' then 1 else 0 ) > 0 ) t on.t.part_# = my_table.part_# and status = 'new' and my_table.Amount <> t.Amount

xQbert · Accepted Answer · 2017-07-19 20:18:12Z

Tried to understand what you want best I could...

SELECT DISTINCT yt.PartNum, yt.Status, yt.Amount FROM YourTable yt JOIN YourTable yt2 ON yt2.PartNum = yt.PartNum AND yt2.Status = 'Old' AND yt2.Amount != yt.Amount WHERE yt.Status = 'New'

This gives everything with a new status that has an old status with a different price.

I think the amounts actually need to match (=, not !=) and your join would need to be a left join and where clause would need yt2.partNum is null. you want where partnum of old matches partnum of new and prices match where you have a new part w/o an old matching.

Collectives™ on Stack Overflow

Nested SQL Queries with Self JOIN - How to filter rows OUT

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Linked

Related