CODEWITHSUNDEEP
mysqlselect
PHPeer
PHPeer

Reputation: 659

Select multiple columns from a row that are based on same related table

Given the following example:

fav_colors
-----------------------
id   col1   col2   col3
-----------------------
01    01     03     03
02    04     02     01
03    01     03     02

colors
-----------
id   colors
-----------
01   green
02   red
03   blue
04   orange

What kind of SELECT statement works to pull the string values from colors for all 3 colors of a particular ID in the fav_colors table?

Something like:

SELECT col1, col2, col3
FROM fav_colors
INNER JOIN ?
WHERE fc.id = 03;

I'm guessing a fav_color array would make this easier, but am relying on these values being separate columns. How do you join the same table to multiple columns in another table?

Edit: All the answers below technically work. Agree that if relying heavily on multiple color info, you're better off recording each color as a referenced row in fav_colors. Thanks!

Upvotes: 0

Views: 794

Answers (3)

Darshana
Darshana

Reputation: 2548

SELECT (SELECT color FROM colors WHERE id = col1) AS color1,
(SELECT color FROM colors WHERE id = col2) AS color2,
(SELECT color FROM colors WHERE id = col3) AS color3
FROM fav_colors WHERE id = 03;

I change column name to color in colors table

Upvotes: 1

hkf
hkf

Reputation: 4520

Three joins to different columns does the trick:

SELECT c1.colors AS c1, c2.colors AS c2, c3.colors AS c3
FROM fav_colors AS fc
INNER JOIN colors AS c1 on c1.id = fc.col1
INNER JOIN colors AS c2 on c2.id = fc.col2
INNER JOIN colors AS c3 on c3.id = fc.col3
WHERE fc.id = 03;

Keep in mind that this is quite bad table design (not scalable at all).

SQLFiddle: http://sqlfiddle.com/#!2/5b01b/6

Upvotes: 4

Jonathan Leffler
Jonathan Leffler

Reputation: 755026

Table aliases...

SELECT fc.id, fc.col1, c1.colors, fc.col2, c2.colors, fc.col3, c3.colors
  FROM fav_colors AS fc
  JOIN colors AS c1 ON fc.col1 = c1.id
  JOIN colors AS c2 ON fc.col2 = c2.id
  JOIN colors AS c3 ON fc.col3 = c3.id
 WHERE fc.id = 03;

Ideally, you'd have a fav_colors table more like:

CREATE TABLE fav_colors
(
    id    INTEGER NOT NULL REFERENCES Users, -- Hypothetical table defining id values
    seq   INTEGER NOT NULL CHECK(seq BETWEEN 1 AND 3),
    color INTEGER NOT NULL REFERENCES Colors
    PRIMARY KEY(id, seq)
);

You might have to tweak some of that syntax for your particular DBMS.

Upvotes: 2

Related Questions