CODEWITHSUNDEEP
mysql
NewbieCoder
NewbieCoder

Reputation: 706

MySQL - Table Query Inner Joining to itself

enter image description here

Consider the above query result,

Is there a way I can join the table itself to get the following results:- 

POH_ID       | JOH_ID       | .............
-------------------------------------------
NULL         | JOH_00000002 | .............
POH_00000002 | JOH_00000001 | .............
POH_00000001 | JOH_00000001 | .............

Meaning, if there's only a single JOH_ID, I retrieve that particular row, if there's more than one of the same JOH_ID, I retrieve the ones with POH_ID.

The result in the photo is a result of a query

Upvotes: 0

Views: 27

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You could find count of rows with same joh_id, join it with main table to filter the rows which have either only one row per joh_id or non-null poh_id

select t.*
from your_table t
join (
    select joh_id, count(*) as cnt
    from your_table
    group by joh_id
    ) t2 on t.joh_id = t2.joh_id
where t2.cnt = 1 or t.poh_id is not null;

Upvotes: 1

Related Questions