CODEWITHSUNDEEP
mysqlsqlself-join
XoXo
XoXo

Reputation: 1599

Getting a row based on a certain condition in sql self-join

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

Answers (2)

fthiella
fthiella

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

Aleks G
Aleks G

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

Related Questions