Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

complex (?) SQL query: data from multiple tables

how to get data from more than one table

3:46 pm on Sep 28, 2005 (gmt 0)

5+ Year Member

I am a beginner in complex SQL queries.

I have a mySQL database with 3 tables: manufacturer, category and products. the manufacturer table has a manufacturer_ID and manufacturer_name. the category table has an category_ID and category_name. the products table has a product_ID, product_name, manufacturer_ID, category_ID and some other fields.

I need a guery that will link (join?) the 3 tables, show all the products in product_ID order, displaying the product_ID, product_name, manufacturer_name and category_name. the kicker (for me) is substituting the manufacturer_name for manufacturer_ID and category_name for category_ID.

thanks for any help

3:56 pm on Sep 28, 2005 (gmt 0)

WebmasterWorld Senior Member txbakers is a WebmasterWorld Top Contributor of All Time 10+ Year Member

Hi and welcome to the Webmaster World!

Queries can be intimidating at first, but the SQL language (pronounced Ess-Cue-ell officially, but I prefer Squeal) is really very easy to understand.

The basic way to get info from a database is with the SELECT statement.

SELECT product_name FROM products.

this returns all the names from the products table in I/O order.

You can order your results by adding an ORDER BY clause at the end:

SELECT product_name FROM products ORDER BY product_ID.

this gives you the same results as above, but ordered by ID.

But, as you said and is proper database design, there are multiple tables. So you need to JOIN them together and get the results.

Hopefully, when the tables were designed, there is a field that is the same in both tables. In your case the mfr table is the "key" to finding the joins.

SO, to get your data, you would need to join the tables together on the same fields in both (the KEY) using a WHERE clause.

SELECT prodID, prodName, mfrName,catName FROM products, mfrs, categories WHERE products.product_id = mfrs.product_ID AND mfrs.category_ID = categories.category_ID ORDER BY prodID.

(I didn't feel like typing all your table and field names - I trust you can figure it out).

Notice that in my SELECT clause, I didn't bring in the category ID. You don't want to display that, so you don't need to bring it in your query. I did include the product ID, since we want to ORDER BY that field. Some dbs require the field to be in the SELECT in order to ORDER BY it.

Hope that gets you going. The "key" to your query is knowing which fields match across the tables and setting the WHERE clause appropriately.

4:43 pm on Sep 28, 2005 (gmt 0)

5+ Year Member

txbakers - thank you for responding. FYI - I do have a pretty reasonable basic understanding of the process. the operative word was "complex". <wry grin>

the products table has manufacturer_ID and category_ID fields. those are in the manufacturer and category tables respectively. I want to get all the products (for now) and display the info I described, substituting the names for the ID's where I described.

I keep thinking that the following would do the job:

SELECT products.product_ID, manufacturer.manufacturer_name, category.category_name
FROM products, manufacturer, category
WHERE product.manufacturer_ID = manufacturer.manufacturer_ID AND products.category_ID = category.category_ID
ORDER BY products.product_ID

unfortunately there is no result from the query and I know that there are matching category_ID's and manufacturer_ID's in their respective tables.

5:05 pm on Sep 28, 2005 (gmt 0)

5+ Year Member

actually this query worlks partly:

SELECT products.product_ID, manufacturer.manufacturer_name
FROM products
LEFT JOIN manufacturer ON products.manufacturer_ID = manufacturer.manufacturer_ID
ORDER BY products.product_ID

however it only displays the product & manufacturer info. I need it to get the category_name as well. is there a way to do 2 joins?


5:25 pm on Sep 28, 2005 (gmt 0)

5+ Year Member

I figured it out, so I'm posting the solution. actually it turns out that you CAN have multiple JOIN statements in mySQL. it seems to work fine. following is my query statement.


SELECT products.product_ID, manufacturer.manufacturer_name, category.category_name
FROM products
LEFT JOIN manufacturer ON products.manufacturer_ID = manufacturer.manufacturer_ID
LEFT JOIN category ON products.manufacturer_ID = category.category_ID
ORDER BY products.product_ID

11:40 pm on Sep 28, 2005 (gmt 0)

10+ Year Member

you can have hundreds of joins if you want :)
just be very carefull when designing your DB when you suspect there will be multiple tabls joins; if you have columns with the exact same field name in multiple tables and want to use them display them say like $row['added_by'], you are going to run into unexpected results.

If the duplicate column name is part of a JOIN ON clause there is no problem (well there is, but PHP will echo it out with a mysql_error()), but if that is not the case , you will search the code for hours looking why the echoed html is displaying unexpected results :)

Ive learned the hard way that for fields likely to appear in multiple tables like (added_by/submitted_by/user_id/added_on/submitted_on, stuff like that), it is good practice to add an abbreviation in front of the fieldname corresponding with the table name

so if you have a table cd_titles with 5 fields whic is part of queries which consist of JOIN clauses, name them like cdt_added_by, cdt_added_on, so you are forced to echo out like: echo $row['cdt_added_by'];

a bit off topic, but I had never thought about this and when I started to add some fields to certain tables, I ran itno huge problems