Re: Obtaining two rows from two different columns in same table

On 29/04/15 17:20, MacMax wrote:
> Someone can help me?
>
> In one table (rel) i have this structure.
> All three fields are numeric.
>
> id, co1, co2
>
> In another table (users) i have the names...
> id_co, name
> id_co, name
> etc.
>
> I would like this result:
>
> id, name (co1)
> id, name (co2)
>
> How i can get this?

Something like this:

(SELECT table1.id, table2.name FROM table1 INNER JOIN table2 ON table1.co1 = table2.id_co)
UNION
(SELECT table1.id, table2.name FROM table1 INNER JOIN table2 ON table1.co2 = table2.id_co)
ORDER BY table1.id

Haven't tested it as I'm to lazy...

If I had been you, I would have made table1 to have

id, number, id_co

then you would have:

SELECT table1.id, table2.name FROM table1 INNER JOIN table2 ON table1.id_co = table2.id_co ORDER BY table1.id, table1.number

This makes it easier for you if you want to add 5 more "columns", as you don't have to modify the table, just make the value "number" bigger for each new column.

-- 

  //Aho