Colors as t1 Categories as t2
+----+-------------+ +----+--------+
| id | name | | id | name |
+----+-------------+ +----+--------+
| 01 | White | | 01 | Light |
| 02 | Blue | | 02 | Dark |
| 03 | Red | | 03 | Normal |
| 04 | Lt White | +----+--------+
| 05 | Lt Blue |
| 06 | Lt Red |
| 07 | Dk White |
| 08 | Dk Blue |
| 09 | Dk Red |
+----+-------------+
Color_Categories as t3 Color_Orders as t4
+--------+--------+ +--------+--------+-------+
| col_id | cat_id | | col_id | cat_id | order |
+--------+--------+ +--------+--------+-------+
| 01 | 03 | | 01 | 03 | 03 |
| 02 | 03 | | 02 | 03 | 02 |
| 03 | 03 | | 03 | 03 | 01 |
| 06 | 01 | | 07 | 02 | 02 |
| 07 | 02 | | 08 | 02 | 01 |
| 08 | 02 | +--------+--------+-------+
| 09 | 02 |
+--------+--------+
Table Color_Categories: contains colors linked to a categorie. may not contain all colors
Table Color_Order: contains colors and their linked category and an order number. may not contain all the colors or categories
Result that i need:
List Of Colors ordered by order within a category (ex.: 02)
Result
+----------+
| Dk Red | (order = null)
| Bk Blue | (order = 01)
| Bk White | (order = 02)
+----------+
tried this:
Select t1.name from t1
left join t3 on t3.col_id = t1.id
left join t4 on t4.col_id = t3.col_id
where t3.cat_id = 02
order by t4.order
but i get repeated records like this
+----------+
| Bk Blue | (order = 01)
| Bk White | (order = 02)
| Dk Red | (order = null)
| Bk Blue | (order = 01)
| Bk White | (order = 02)
| Dk Red | (order = null)
+----------+