Miguel Vaz wrote:
I have three tables:
TABLE Person
- id_person, name, id_levelA, id_sizeA, id_levelB, id_sizeB
TABLE Levels
- id, desc
TABLE Sizes
- id, desc
Hi! You can always join a table twice :)
SELECT
p.id_person
, lA.desc as levelA
, sA.desc as sizeA
, lB.desc as levelB
, sB.desc as sizeB
FROM Person p
INNER JOIN Levels lA
ON p.id_levelA = lA.id
INNER JOIN Levels lB
ON p.id_levelB = lB.id
INNER JOIN Sizes sA
ON p.id_sizeA = sA.id
INNER JOIN Sizes sB
ON p.id_sizeB = sB.id;
Of course, if id_levelA field is NULLable, you would use a LEFT JOIN
instead of an INNER JOIN.
Here is an example output:
mysql> CREATE TABLE Person (
-> id_person INT UNSIGNED NOT NULL
-> , name VARCHAR(20) NOT NULL
-> , id_levelA TINYINT UNSIGNED NOT NULL
-> , id_sizeA TINYINT UNSIGNED NOT NULL
-> , id_levelB TINYINT UNSIGNED NOT NULL
-> , id_sizeB TINYINT UNSIGNED NOT NULL
-> , PRIMARY KEY (id_person)
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO Person VALUES (1, 'Miguel', 1, 1, 2, 2);
Query OK, 1 row affected (0.04 sec)
mysql> CREATE TABLE Levels ( id TINYINT UNSIGNED NOT NULL , `desc`
VARCHAR(20) NOT NULL , PRIMARY KEY (id) );
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE Sizes ( id TINYINT UNSIGNED NOT NULL , `desc`
VARCHAR(20) NOT NULL , PRIMARY KEY (id) );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO Levels VALUES (1, 'Level One'),(2, 'Level Two');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO Sizes VALUES (1, 'Size One'),(2, 'Size Two');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT
-> p.id_person
-> , lA.desc as levelA
-> , sA.desc as sizeA
-> , lB.desc as levelB
-> , sB.desc as sizeB
-> FROM Person p
-> INNER JOIN Levels lA
-> ON p.id_levelA = lA.id
-> INNER JOIN Levels lB
-> ON p.id_levelB = lB.id
-> INNER JOIN Sizes sA
-> ON p.id_sizeA = sA.id
-> INNER JOIN Sizes sB
-> ON p.id_sizeB = sB.id;
+-----------+-----------+----------+-----------+----------+
| id_person | levelA | sizeA | levelB | sizeB |
+-----------+-----------+----------+-----------+----------+
| 1 | Level One | Size One | Level Two | Size Two |
+-----------+-----------+----------+-----------+----------+
1 row in set (0.00 sec)
A couple notes for you:
1) "desc" is a keyword, so I would not recommend using it as a field
name. Use something like "description" instead to make your life easier
2) This kind of table structure is typically an indication of a poorly
designed schema because it is not normalized. You should instead have a
table, PersonLevels and PersonSizes, which can store any number of a
person's levels and sizes... read up on normalization about this concept.
Cheers,
Jay
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
