MySQL Select Based on Conditional Select

Question

I have my structure set up so that any entry could contain a reference to another entry to fill in the missing details.

ID,  NAME, DESCRIPTION, REFERENCE
0,  Stuff,      Things, -1
1,       ,            , 0
2, Things,       Stuff, -1

I want to be able to return all the results for the table and if an entry requires a reference to another entry to complete the data, to fill the reference field with the data. I also want to keep the original ID of the reference field. I also want the fields that don't require a reference to be left alone.

SELECT `ID`, `REFERENCE` FROM `details` as `t`
     IF `REFERENCE` != -1 THEN
          SELECT *, `t`.`REFERENCE` as `ORIGINAL_ID` FROM `details` WHERE `ID` = `t`.`REFERENCE`
     ELSE
          SELECT * FROM `details` WHERE `ID` = `t`.`ID`

Obviously that won't work, but I'm just trying to demonstrate to you what I think I want. Is this possible? Thanks for reading.

Ed Gibbs · Accepted Answer · 2013-04-26 14:12:28Z

1

You can get close with something like this:

SELECT
    t.ID,
    t.Reference,
    COALESCE(reffed.Name, t.name) AS name,
    COALESCE(reffed.whatever, t.whatever) AS whatever,
    COALESCE(... and so on ...)
  FROM details t
  LEFT OUTER JOIN details reffed ON t.Reference = reffed.id

If there's a referenced value it will take precedence over the "base" value because of the COALESCE.

If Reference = -1 or if Reference doesn't point to a valid id the referenced value will be null so COALESCE will return the "base" value (t.whatever instead of reffed.whatever).

Note that this assumes you won't have an actual row where details.id = -1.

answered Apr 26, 2013 at 14:12

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2013-04-26 14:05:17Z

0

I think you just want a left outer join and some logic in the select statement.

The following code adds two new columns. These incorporate the logic that they pull from the referenced row, if any, and then from the original data:

select d.*,
       (case when Reference >= 0 then dref.name else d.name end) as UsedName,
       (case when Reference >= 0 then dref.description else d.description end) as UsedDescription
from details d left outer join
     details dref
     on d.reference = dref.id

answered Apr 26, 2013 at 14:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

MySQL Select Based on Conditional Select

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related