By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,967 Members | 2,124 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,967 IT Pros & Developers. It's quick & easy.

Help making a multi table query

P: 2
I am doing a php webshop.
I have 4 tables: product for product info, product_catalog for multi-level catalogs,
product_product_catalog for binding product and product_catalog together -- it shows which product is in what catalog


I want to select from product: name, price, created(date), updated(date)
and first_name, last_name from employee (2 times one for creted_by and one for updated_by)

So far I have come this far:

SELECT product.name, product.price, product.created, product.updated, product.created_by, product.updated_by, product.product
FROM product INNER JOIN product_product_catalog ON product.product = product_product_catalog.product
WHERE product_product_catalog.product_catalog=".$subcat. "

but it selects the integer values of created_by and updated_by not the actual names linked to them


To show what the tables look like(there are other values there but not important now):

Expand|Select|Wrap|Line Numbers
  1. Table product
  2.  
  3. select product, price, created, updated, created_by, updated_by from product limit 5;
  4.  product |    price    |          created           |          updated           | created_by | updated_by
  5. ---------+-------------+----------------------------+----------------------------+------------+------------
  6.        1 | 2330.000000 | 2009-07-27 10:02:11.764316 | 2009-09-15 11:17:26.400745 |          1 |          2
  7.       40 | 4020.000000 | 2009-07-27 11:16:01.781094 |                            |            |
  8.       41 | 3244.000000 | 2009-07-27 11:17:06.054212 |                            |            |
  9.        2 | 2380.000000 | 2009-07-27 10:05:08.319431 |                            |            |
  10.        3 | 1430.000000 | 2009-07-27 10:06:25.818154 |                            |            |
  11.  
  12.  
  13. Table product_catalog
  14.  
  15. select product_catalog, upper_catalog, name from product_catalog limit 3;
  16.  product_catalog | upper_catalog |       name
  17. -----------------+---------------+-------------------
  18.                1 |             0 | Arvutikomponendid
  19.                2 |             1 | Emaplaadid
  20.                3 |             1 | Protsessorid
  21.  
  22. Table product_product_catalog
  23.  
  24. select product_product_catalog, product_catalog, product from product_product_catalog limit 5;
  25.  product_product_catalog | product_catalog | product
  26. -------------------------+-----------------+---------
  27.                        2 |               2 |       2
  28.                        3 |               2 |       3
  29.                        4 |               2 |       4
  30.                        5 |               2 |       5
  31.                        6 |               2 |       6
  32.  
  33. Table employee
  34. # select employee, first_name ,last_name from employee;
  35.  employee | first_name | last_name
  36. ----------+------------+-----------
  37.         1 | Jaan       | Jalgratas
  38.         2 | John       | Smith
  39. (2 rows)
Thanks for helping
Sep 15 '09 #1
Share this Question
Share on Google+
1 Reply


P: 2
Hei I just got it!

It is:

Expand|Select|Wrap|Line Numbers
  1. SELECT p.name, p.price, p.created, p.updated, p.product,
  2. creator.first_name as etor_fn, creator.last_name as em_creator_ln,
  3. updater.first_name as em_updater_fn, updater.last_name as em_updater_ln
  4. FROM product as p INNER JOIN 
  5. (product_catalog INNER JOIN product_product_catalog ON product_catalog.product_catalog = product_product_catalog.product_catalog)
  6. ON p.product = product_product_catalog.product
  7. LEFT JOIN employee as creator ON p.created_by = creator.employee
  8. LEFT JOIN employee as updater ON p.updated_by = updater.employee
  9. WHERE product_catalog.catalog = ".$subcat.";

I learned it from other posts of this forum, but at the beginning I thought it would be much more complicated

Thanks for the help! Problem solved
Sep 15 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.