self join table

Question

HI,

i have a table,

    new_id----old_id----created_on
    1234------5678------20100912
    5678------3456------20100808

etc.

i wrote this query,

$q = "select event1.new_id, event1.old_id, event1.created_on,event2.new_id, event2.old_id, event2.created_on
  FROM replaced_isbns event1
  JOIN replaced_isbns event2 
  ON event2.new_id = event1.old_id
  WHERE event1.new_id='$id'";

but i get result as 5678 - 1234, ie only one row, i want all rows...

@webdestroya,

it gives only one row.... i want output like this, For new_id 1234, old_id's are 5678,3456 etc

plz help me. Thanks, SLC

Answer 1

if you want all rows than try left outer join and replace null with new id for example

SELECT
    event1.new_id, event1.old_id, event1.created_on,
    IFNULL(event2.new_id, event1.new_id),
    IFNULL(event2.old_id, event1.old_id),
    event2.created_on
FROM replaced_isbns event1
LEFT OUTER JOIN replaced_isbns event2 
  ON event2.new_id = event1.old_id
WHERE event1.new_id='$id'

check this may help you

Answer 2

Why make a self join?

SELECT replaced_isbns.*
FROM replaced_isbns
WHERE new_id = '$id'
OR old_id = '$id'

Now, for each row in your result check if new_id = $id, or if old_id = $id.

Answer 3

Hello,

As far as i understood, you need all the old_ids for the new_id:- for new_id:- 1234 old id' are 5678 and 3456. similarly for second record:- for new_id:- 5678 old id' are 3456. I have tested this and it is working fine.

select new_id,b.old_id from 
   (select old_id from temp)
     as b inner join temp as a 
       on a.new_id!=b.old_id

Do let me know if this is the one you wanted or not. I hope this helps!

Answer 4

You won't be able to get the result you want using a query since there's no way to query recursive relations in SQL (well, ok, Oracle has START WITH ... CONNECT BY ... syntax and CTE, but I guess you're not using Oracle :p).

But on the other hand you could try to convert this adjacency list into a nested set, because getting a branch (all child records of a given parent) is very easy with nested sets. Managing Hierarchical Data in MySQL is an interesting article on that subject.