CODEWITHSUNDEEP
mysqlsql
DarkShadowAY
DarkShadowAY

Reputation: 175

A simpler way of joining multiple columns to one look up table

I have two tables as follows: 

 TablePC

+------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| PCID | Option1 | Option2 | Option3 | Option4 | Option5 | Option6 | Option7 | Option8 | Option9 |
+------+---------+---------+---------+---------+---------+---------+---------+---------+---------+
| 1    | JM2     | JM3     | JM4     | JM5     |         |         |         |         |         |
+------+---------+---------+---------+---------+---------+---------+---------+---------+---------+

and 

 Options

+----------+---------------+----------------+
| OptionID |  Description  | ExtendedDetail |
+----------+---------------+----------------+
| JM2      | Graphics Card | ATI X1950XT    |
+----------+---------------+----------------+

Instead of writing something like 

SELECT Code,Description FROM 

(SELECT * FROM TablePC) c
JOIN 
(SELECT * FROM Options) o
ON c.Option1 = o.Code
OR c.Option2 = o.Code
OR c.Option2 = o.Code
OR c.Option3 = o.Code
OR c.Option4 = o.Code
OR c.Option5 = o.Code
OR c.Option6 = o.Code
OR c.Option7 = o.Code
WHERE c.PCID = 1

Is there a cleaner,simpler way of writing this or is this acceptable? I have 12 option fields.

p.s. How does one format sql tables/results as I've seen in other questions? I had to write and indent that for code.

Thanks

Upvotes: 0

Views: 48

Answers (2)

GarethD
GarethD

Reputation: 69819

Well normalisation aside, you could tidy this up simply by using IN:

SELECT Code,Description FROM 

(SELECT * FROM TablePC) c
JOIN 
(SELECT * FROM Options) o
ON o.Code IN (c.Option1, c.Option2, c.Option3, c.Option3, c.Option4, c.Option5, c.Option6, c.Option7, c.Option8)
WHERE c.PCID = 1;

Also your subqueries are redundant and will hinder performance. MySQL will put the results of subqueries into temporary tables (derived tables) at the start of execution, so in a simple example:

SELECT *
FROM T
WHERE T.ID = 1;

SELECT *
FROM (SELECT * FROM T) T
WHERE T.ID = 1;

You put the entire contents of T into a temporary table, only to select one row from that. If you check the query plan on this SQL Fiddle you will see the second query is not even able to use the primary key index on ID to perform the filter.

As such your query should be more like:

SELECT Code, Description 
FROM TablePC c
    JOIN Options o
        ON o.Code IN (c.Option1, c.Option2, c.Option3, c.Option3, c.Option4, c.Option5, c.Option6, c.Option7, c.Option8)
WHERE c.PCID = 1;

Upvotes: 1

Jayvee
Jayvee

Reputation: 10873

you can also use 'in' :

SELECT Code,Description FROM 

(SELECT * FROM TablePC) c
JOIN 
(SELECT * FROM Options) o
ON o.Code in (c.Option1,c.Option2,c.Option3,c.Option4,c.Option5,c.Option6,c.Option7)
WHERE c.PCID = 1

Upvotes: 0

Related Questions