MYSQL join two tables

Question

I have two tables one containing a selection of values in different categories and the other ‘master’ table referencing the text values by the first primary key.

Table 1

CREATE TABLE IF NOT EXISTS `defaultvalues` (
  `default_ID` int(11) NOT NULL AUTO_INCREMENT,
  `columnName` varchar(100) NOT NULL,
  `defaultValue` varchar(100) NOT NULL,
   PRIMARY KEY (`default_ID`),
  UNIQUE KEY `columnName` (`columnName`,`defaultValue`)
)

Table 2

CREATE TABLE IF NOT EXISTS `master` (
  `master_ID` int(11) NOT NULL AUTO_INCREMENT,
`size` int(11) NOT NULL,
 `madeby` int(11) NOT NULL,
 `type` int(11) NOT NULL,
  `colour` int(11) NOT NULL,
  `notes` text NOT NULL,
 `issueDate` datetime NOT NULL,
 `ceMark` text NOT NULL,
`batchNumber` text NOT NULL,

PRIMARY KEY (master_ID) )

The master.size for each row is a P.key in the defaultvalues table.

E.g. master.colour = 234, 234=defaultvalues.defaultValue = ‘red’

E.g. master.size = 345, 345=defaultvalues.defaultValue = ‘small’

Now I would like to run a query that returns the ‘master’ table with text values in columns colour, size, type, madeby from ‘defaultvalues. defaultValue’ and ready for further processing.

I have been trying with sub queries and temp tables but I can’t get it to work

The current system relies on PHP and multiple queries and building arrays.

There has to be a more elegant solution.

I hope this makes sense.

Any hints or advice much appreciated.

Dave

Answer 1

What i did in the end.... while it works I am still not happy. There must be something better...

SELECT m.*,

(SELECT defaultValue FROM defaultvalues WHERE default_ID = m.colour) AS myColour , (SELECT defaultValue FROM defaultvalues WHERE default_ID = m.size) AS mySize

FROM master m WHERE m.master_ID = 1;

Answer 2

You'll need to join the master table to the defaultvalues table multiple times. Something like this:

SELECT m.*, d.defaultvalue as sizevalue, d2.defaultvalue as colorvalue...
FROM master m
   JOIN defaultvalues d ON m.size = d.default_id
   JOIN defaultvalues d2 ON m.color = d2.default_id
   ...