mysql select query on two tables

Question

I am trying to get a result from two tables without having a second query nested inside the first query loop.

I have a table products:

product_code_1234 | product_name | otherfields...

And a table categories, where a product can have multiple categories:

category_name_1 | product_code_1234
category_name_2 | product_code_1234
category_name_3 | product_code_1234

Is there a query to get the following result?

product_code_1234 | product_name | ... | category_name_1 | category_name_2 | category_name_3

Solution 1

select * from a,b 

will give you all data from table a ,combined with all data from table b.

However if you don't want repetition of data and there isn't any connection between table a and b it can't be done without some union or similar

Solution 2

Assume you have these tables:

+----------------------------+
| PRODUCTS                   |
+------+-------------+-------+
| code | name        | price |
+------+-------------+-------+
| 1    | Bike Helmet | 99.99 |
| 2    | Shirt       | 19.99 |
+------+-------------+-------+

+-------------------+
| CATEGORIES        |
+------+------------+
| code | category   |
+------+------------+
| 1    | Sports     |
| 1    | Cycling    |
| 1    | Protection |
| 2    | Men        |
| 2    | Clothing   |
+------+------------+

---

Here is a query based on another SO answer, that would match your desired result, if my interpretation of it is correct:

SELECT p.code, p.name, p.prize,
    (SELECT category FROM categories c WHERE c.code = p.code LIMIT 0, 1) as category1,
    (SELECT category FROM categories c WHERE c.code = p.code LIMIT 1, 1) as category2,
    (SELECT category FROM categories c WHERE c.code = p.code LIMIT 2, 1) as category3,
FROM products p

This is the result:

+------+-------------+-------+-----------+-----------+------------+
| code |    name     | price | category1 | category2 | category3  |
+------+-------------+-------+-----------+-----------+------------+
|    1 | Bike Helmet | 99.99 | sports    | cycling   | protection |
|    2 | Shirt       | 19.99 | men       | clothing  | NULL       |
+------+-------------+-------+-----------+-----------+------------+

It is not possible, though, to have a dynamic number of categories in the result. In this case I limited the number to 3, like in your question. There might still be a solution with better performance. Also, this is obviously a nested query and therefore probably not suited for your needs. Still, it's the best I could come up with.

JOIN

There is also the SQL JOIN clause, which might be what you are looking for:

SELECT *
FROM products
NATURAL JOIN categories

You would end up with this result:

+------+-------------+-------+------------+
| code | name        | price | category   |
+------+-------------+-------+------------+
| 1    | Bike Helmet | 99.99 | sports     |
| 1    | Bike Helmet | 99.99 | cycling    |
| 1    | Bike Helmet | 99.99 | protection |
| 2    | Shirt       | 19.99 | men        |
| 2    | Shirt       | 19.99 | clothing   |
+------+-------------+-------+------------+

Comments

[Sclerato]

That doesnt look to fit my needs, i get duplicate product records, one for each category.

[Edward Wong]

Like I wrote "However if you don't want repetition of data and there isn't any connection between table a and b it can't be done without some union or similar"

[Sclerato]

Oh yes sorry, guess i need two nested queries then, tables are quite different from each other to use an union. Thanks!

[Edward Wong]

np, if I helped you can select this as good answer to your question ;)