Query row against a row in same table

[SharkBait]

Trying to figure out how to create a query that queries the same table. A bit confusing right?

 

  id     |     parent_id     |     serial
-------------------------------------
  320   |    0                | 43443
-------+---------------+------------
  423   |    320             |  00ff003300dd
-------+---------------+------------

What i need to do is link those two rows together in 1 query so that I can extract the two `serial` values. As you can see parent_id is what links them together.

 

How can this be done with 1 query?

 

I can do it with 2 separate queries but I would like to know how/if it can be done with 1.

 

Thanks

 

 

[Barand]

... FROM tablename as a JOIN tablename as b ON a.parent_id = b.id

[AjBaz100]

Could try :

 

select serial from table where id in ( select parent_id from table ) or parent_id in ( select id from table )

 

I don't think this would be very efficient though.

[bluejay002]

Yes, Barand's solution is the best one for you (I think? ). The second solution though is possible but not recommended.

[Barand]

the full query would be

 

SELECT a.serial, b.serial as parent_serial

FROM tablename as a

JOIN tablename as b ON a.parent_id = b.id