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

Can you post some sample data and then the desired result? – Taryn Jan 24 '13 at 16:19 — Taryn, Jan 24 '13 at 16:19

score 2 · Accepted Answer · answered Jan 24 '13 at 16:03

2

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

answered Jan 24 '13 at 16:03

Edward Wong

46
3

That doesnt look to fit my needs, i get duplicate product records, one for each category. – Sclerato Jan 24 '13 at 16:15
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" – Edward Wong Jan 24 '13 at 16:28
Oh yes sorry, guess i need two nested queries then, tables are quite different from each other to use an union. Thanks! – Sclerato Jan 24 '13 at 16:32
np, if I helped you can select this as good answer to your question ;) – Edward Wong Jan 24 '13 at 16:34

score 2 · Answer 2 · edited May 23 '17 at 12:23

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   |
+------+-------------+-------+------------+

score 0 · Answer 3 · answered Jan 24 '13 at 16:36

0

I guess that you will have to do two separate queries.

One to retrieve products, one to retrieve the product categories.

Then use any scripting language (like PHP) to achieve what you want with the results (display, export, whatever).

answered Jan 24 '13 at 16:36

Maxime Pacary

22,336
11
85
113

mysql select query on two tables

3 Answers3

JOIN