CODEWITHSUNDEEP
sql
MmynameStackflow
MmynameStackflow

Reputation: 1251

MySQL Query: How to do this one?

I know this table isn't as it should be but it just is and it can't be changed.

My Table:

|carID  | car1      | car2      | car3      |
-------------------------------------------------
|   1   | someCar 1 | someCar 2 | someCar 3 |
-------------------------------------------------

And I want the following result like it should be in a relation table:

|carID  |car        |
---------------------
|1      | someCar 1 |
|1      | someCar 2 |
|1      | someCar 3 |

Does someone know how to do this?

I tried something like this:

SELECT carId, car1, car2, car3 FROM cars WHERE carId = '1' GROUP BY car1, car2, car3

Upvotes: 0

Views: 44

Answers (3)

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

Create a union of three selects:

SELECT carID, car1 FROM cars WHERE carID = '1'
UNION ALL
SELECT carID, car2 FROM cars WHERE carID = '1'
UNION ALL
SELECT carID, car3 FROM cars WHERE carID = '1'

Please note that you need to use UNION ALL, not just UNION, because UNION alone would leave out duplicates. For example, if car2 and car3 were the same, the result would only contain two records.

Upvotes: 0

xlecoustillier
xlecoustillier

Reputation: 16351

Try UNION ALL :

SELECT CarID, Car1 FROM myTable WHERE carID='1'
UNION ALL
SELECT CarID, Car2 FROM myTable WHERE carID='1'
UNION ALL
SELECT CarID, Car3 FROM myTable WHERE carID='1'

Upvotes: 0

John Woo
John Woo

Reputation: 263723

UNION

SELECT   CarID, car1 car FROM MyTable
UNION ALL
SELECT   CarID, car2 car FROM MyTable
UNION ALL
SELECT   CarID, car3 car FROM MyTable

Upvotes: 2

Related Questions