CODEWITHSUNDEEP
mysql
Michel
Michel

Reputation: 4157

Select from same table, different rows, different columns

My MySQL skills are very basic, so What I need (if possible) is a single SELECT statement that selects different columns from different rows in the same table. Most Google comes up with has to do with combining different tables or selecting same columns from different rows.

Table

id  |  c1  |  c2  |  c3  |  c4  |  c5  |  c6  |  c7  
-----------------------------------------------------   
1   | THIS | THIS | THIS |   a  |   a  |   a  |   a  
2   |   a  |   a  |   a  |   a  | THIS | THIS |   a

I'd like to select columns c1, c2, c3 from row 1 and c5, c6 from row 2. Column names and id numbers are known.

I could fire two queries and combine the data:

   SELECT c1,c2,c3 FROM table WHERE id=1
   SELECT c5,c6 FROM table WHERE id=2

or I can fetch all the columns from the two rows simultaniously and filter out the data I need afterwards

   SELECT c1,c2,c3,c5,c6 FROM table WHERE id=1 OR id=2

My question: is there a SELECT statement that combines the two so I get the combined results in one query?

I found out UNION could work, but that only works if the number of columns selected is the same in both select-statements. And in this case, the aren't.

Any help would be appreciated.

[edit] See my answer below.

Upvotes: 3

Views: 15922

Answers (3)

Michel
Michel

Reputation: 4157

The answers above still didn't do exactly what I wanted, because those queries returned two rows and/or messed with the column names.

In the end it turned out to be pretty simple:

SELECT * 
FROM (SELECT c1,c2,c3 FROM table WHERE id=1) AS t1,
FROM (SELECT c5,c6 FROM table WHERE id=2) AS t2

it returns one row, containing the values for c1, c2, c3, c5 and c6 with their corresponding column names

Upvotes: 4

John Ruddell
John Ruddell

Reputation: 25862

you can just do a conditional column select instead of messing with union if you want :)

SELECT
    CASE WHEN id = 1 THEN c1 ELSE c5 END
    CASE WHEN id = 1 THEN c2 ELSE c6 END
    CASE WHEN id = 1 THEN c3 ELSE NULL END
FROM table

Upvotes: 1

knittl
knittl

Reputation: 265585

You can use UNION if you select a constant value for the missing fields:

SELECT c1, c2, c3 FROM table WHERE id=1
UNION ALL
SELECT c5, c6, NULL c3 FROM table WHERE id=2

With your sample data this will give you:

c1   c2   c3
THIS THIS THIS
THIS THIS NULL

Upvotes: 2

Related Questions