mysql query

mattca · 06-15-2010, 12:29 PM

Hi all, I have what I hope is a simple question. I'm kind of new to working with joins so this is probably an easy question..

I have a database structure set up for an online shop. I have a products table, which has an id and several fields of data:

Code:

mysql> describe products;
+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| id                | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| title             | varchar(255)     | YES  |     | NULL    |                | 
| description       | text             | YES  |     | NULL    |                | 
| short_description | text             | YES  |     | NULL    |                | 
| type_id           | int(10) unsigned | NO   |     | NULL    |                | 
| price             | decimal(10,2)    | YES  |     | 0.00    |                | 
| active            | tinyint(1)       | NO   |     | 1       |                | 
| image             | varchar(255)     | YES  |     | NULL    |                | 
| bulk_orders       | tinyint(1)       | NO   |     | 0       |                | 
| featured          | tinyint(1)       | YES  |     | 0       |                | 
+-------------------+------------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)

Some of the products in the store are packages, which contain 3 other products. So I have a products_packages table to connect packages to their products:

Code:

mysql> describe products_packages;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| package_id | int(10) unsigned | NO   | PRI | NULL    |       | 
| product_id | int(10) unsigned | NO   | PRI | NULL    |       | 
+------------+------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

package_id and product_id are both ids from the products table.. ie, products.id.

I also have 2 different tables of related data for the different types of products: books and dvds:

Code:

mysql> describe books;
+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| product_id      | int(10) unsigned | NO   | PRI | NULL    |       | 
| isbn10          | varchar(10)      | YES  |     | NULL    |       | 
| isbn13          | varchar(13)      | YES  |     | NULL    |       | 
| publisher_id    | int(10) unsigned | YES  |     | NULL    |       | 
| publishing_date | date             | YES  |     | NULL    |       | 
| pages           | int(10) unsigned | YES  |     | NULL    |       | 
| dimensions      | varchar(255)     | YES  |     | NULL    |       | 
| format_id       | int(10) unsigned | YES  |     | NULL    |       | 
| preview_url     | varchar(255)     | YES  |     | NULL    |       | 
+-----------------+------------------+------+-----+---------+-------+
9 rows in set (0.03 sec)

mysql> describe dvds;
+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| product_id  | int(10) unsigned | NO   | PRI | NULL    |       | 
| producer_id | int(10) unsigned | YES  | MUL | NULL    |       | 
+-------------+------------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

books.product_id and dvds.product_id are foreign keys for products.id.

The main goal is to get all of the data for all products in one query. I can do this with the one to one relationships, but I'm having a hard time with the one to many, particularly the packages.

For example, for each package I'd like to have a row for each product in the package, each of which has the data for the package, and the data for the particular product.

ie, for each package, I'd like something like this:

Code:

package id, package name, package data (image, description, etc), product #1 id, product #1 name, product #1 data
package id, package name, package data (image, description, etc), product #2 id, product #2 name, product #2 data
package id, package name, package data (image, description, etc), product #3 id, product #3 name, product #3 data

Also note that the data for each individual product should include the data from the books and dvds tables.

How would I go about doing this? And if there's a better way to approach the problem, I'd like to know.

Thanks!

timmeke · 06-16-2010, 05:57 AM

I'm not an SQL expert, so I'm sure there's a better approach. But here's my breakdown:

Code:

select * from products where id in (select package_id from products_packages);

gives you the info on all the packages.

An inner join on products_packages.package_id would do the same:

Code:

select * from products_packages pa, products p where p.id=pa.package_id;

Think of this as "I take all combinations of each of the rows in pa and p (so-called cross-product of the tables) in a new table and then filter out the records where the id match".

Then we need to combine this with the info on the regular products included in each package, taken
from the products table, which is another inner join, this time on products_packages.product_id;

Code:

select * from products_packages pa, products p, products p2 where p.id=pa.package_id and p2.id=pa.product_id;

This should work in theory, but it would be my first time to cross a table with itself ("from products p, products p2 in the query").
You'll see many duplicated columns here, which could be confusing. So you better change the "*" with something more meaningful.

Downside is that it uses inner joins, meaning that you won't get the products that are not in any of the packages.
To add those, a "union" of 2 queries could be a good approach. Note that the 2 parts of the union should produce the same amount & type of columns, so you should fill out the package info columns with NULLs for products that don't appear in any package.
Something like

Code:

select p.id, p.title, p2.id, p2.title from products_packages pa, products p, products p2 where p.id=pa.package_id and p2.id=pa.product_id
union
select NULL, NULL, p.id, p.title from products where p.id not in (select product_id from products_packages);

Don't forget the line continuation at the end.

To add the books / dvds info would make this even more complicated (more inner joins & unions) and hence prone to errors.

A nicer approach could be to write out a stored procedure for this, or to use "views" (think of them as temporary tables, of which the content is defined by a query on real tables and stored as query, not as table with full content).

mattca · 06-23-2010, 10:00 AM

Thanks for the help.. I ended up making a packages table to mirror the books and dvds table.. really it only holds a product id, and might be unnecessary.. but it makes it easier to build queries dynamically based on the type.. ie, packages are no longer a special case.

Still haven't sorted out how to pull everything together in one query though..

Quote:

Originally Posted by timmeke

Code:

select * from products_packages pa, products p where p.id=pa.package_id;

Is there a reason you're not using the join syntax? I ask because I was joining queries using where clauses until recently, and joins have made my life a lot easier:

Code:

SELECT * FROM products LEFT JOIN books ON books.product_id = products.id LEFT JOIN dvds ON dvds.product_id = products.id LEFT JOIN packages ON packages.product_id = products.id;

That pulls in all columns from products, books, dvds and packages - without putting each table in the FROM clause, and without a WHERE clause, which helps distinguish your join conditions from your actual where clause.

timmeke · 06-23-2010, 10:59 AM

I do use the standard inner join, just different syntax.
Yes, your join syntax does help with readability...
but look at how much text you had to type compared to me.