0

I'm trying to advance my knowledge of SQL queries but am getting stuck on a many-to-many relationship query.

Using PHP and MySQL database structure is as follows:

Database structure:

--------------------------------------------
|                  colour                  |
--------------------------------------------
| colour_id         |      colour          |
--------------------------------------------
| 1                 |      blue            |
--------------------------------------------
| 2                 |      red             |
--------------------------------------------
############################################
--------------------------------------------

.

-----------------------------------------------------------------
|   product_colours                                             |
-----------------------------------------------------------------
| id            |     product_id       |    colour_id           |
-----------------------------------------------------------------
| 1             |         1            |         2              |
-----------------------------------------------------------------
| 2             |         2            |         1              |
-----------------------------------------------------------------
#################################################################
-----------------------------------------------------------------

MAIN TABLE

-----------------------------------------------------------------
|   products                                                    |
-----------------------------------------------------------------
| id          |      name               |      details          |
-----------------------------------------------------------------
| 1           |      product 1          |      blah             |
-----------------------------------------------------------------
| 2           |      product 2          |      blah             |
-----------------------------------------------------------------
#################################################################
-----------------------------------------------------------------


-----------------------------------------------------------------
|    product_group_names                                        |
-----------------------------------------------------------------
| id            |      product_id         |    group_name_id    |
-----------------------------------------------------------------
| 1             |          1              |         1           |
-----------------------------------------------------------------
| 2             |          2              |         2           |
-----------------------------------------------------------------
#################################################################
-----------------------------------------------------------------


--------------------------------------------
|        group_name                        |
--------------------------------------------
| group_name_id     |      group_name      |
--------------------------------------------
| 1                 |  product_group_1     |
--------------------------------------------
| 2                 |  product_group_2     |
--------------------------------------------
############################################
--------------------------------------------

Could I write one query using joins which says: SELECT * colours WHERE group_name = product_group_1?

Any help would be amazing. Thank you so much.

Improve this question
1
  • 1
    Yes you could write such a query - what have you tried so far? Commented Apr 18, 2013 at 20:33

1 Answer 1

Reset to default
2

You can certainly write a query to do that. You'll need to basically join your tables together so you can link the data you need from table to table, and then add a where clause for your desired condition.

Your colours are "linked" to your product_colours via the colour_id attribute, the product colours are linked to the product_group_names via the product_id attributes on both tables, and your group names are linked via the group_name_id and group_id attributes on the group_names table.

When you're doing this kind of query though, if you have a lot of records in these tables - the queries can go very slow. You need to make sure you add indicies on each of the columns referenced in the JOIN...ON clauses, as well as the WHERE clause.

SELECT colour FROM colours c
JOIN product_colours pc ON c.id = pc.colour_id
JOIN product_group_names pgn ON pgn.product_id = pc.product_id
JOIN group_name gn ON gn.id = pgn.group_name_id
WHERE gn.group_name = "product_group_1"
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.