Reputation: 1599
For the following table, is it possible to get the result using a self-join?
Table:
id pId type
-------------
1 1000 1
2 1001 1
3 1002 1
4 1000 3
Expected result:
id pId type
-------------
2 1001 1
3 1002 1
In other words, I want all the rows which has type 1, but does not have type 3.
Thank you in advance.
UPDATE
this is a question in the context of a performance testing. in other words, there are many rows like 1000 and 1001, 1002.
i'm trying to improve the performance using the current table structure. but i understand that probably the table is not well-designed as well.
Upvotes: 0
Views: 65
Reputation: 49089
If you want to use a self join, you could use this query:
SELECT t1.id, t1.pId, t1.type
FROM
tablename t1 LEFT JOIN tablename t2
ON t1.pid = t2.pid AND t2.type=3
WHERE
t1.type=1 AND
t2.type IS NULL
Please see fiddle here.
Upvotes: 0
Reputation: 57346
You don't need any joins - just a subselect - something like this:
select * from mytable t1
where not exists (select id from mytable t2 where t1.pid=t2.pid and type=3)
Upvotes: 2