Help making a multi table query

foobar

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

 
Table product
 
select product, price, created, updated, created_by, updated_by from product limit 5;

 product |    price    |          created           |          updated           | created_by | updated_by

---------+-------------+----------------------------+----------------------------+------------+------------

       1 | 2330.000000 | 2009-07-27 10:02:11.764316 | 2009-09-15 11:17:26.400745 |          1 |          2

      40 | 4020.000000 | 2009-07-27 11:16:01.781094 |                            |            |

      41 | 3244.000000 | 2009-07-27 11:17:06.054212 |                            |            |

       2 | 2380.000000 | 2009-07-27 10:05:08.319431 |                            |            |

       3 | 1430.000000 | 2009-07-27 10:06:25.818154 |                            |            |
 
Table product_catalog
 
select product_catalog, upper_catalog, name from product_catalog limit 3;

 product_catalog | upper_catalog |       name

-----------------+---------------+-------------------

               1 |             0 | Arvutikomponendid

               2 |             1 | Emaplaadid

               3 |             1 | Protsessorid
 
Table product_product_catalog
 
select product_product_catalog, product_catalog, product from product_product_catalog limit 5;

 product_product_catalog | product_catalog | product

-------------------------+-----------------+---------

                       2 |               2 |       2

                       3 |               2 |       3

                       4 |               2 |       4

                       5 |               2 |       5

                       6 |               2 |       6
 
Table employee

# select employee, first_name ,last_name from employee;

 employee | first_name | last_name

----------+------------+-----------

        1 | Jaan       | Jalgratas

        2 | John       | Smith

(2 rows)

Thanks for helping

foobar

Hei I just got it!

It is:

Expand|Select|Wrap|Line Numbers

 SELECT p.name, p.price, p.created, p.updated, p.product,

creator.first_name as etor_fn, creator.last_name as em_creator_ln,

updater.first_name as em_updater_fn, updater.last_name as em_updater_ln

FROM product as p INNER JOIN 

(product_catalog INNER JOIN product_product_catalog ON product_catalog.product_catalog = product_product_catalog.product_catalog)

ON p.product = product_product_catalog.product

LEFT JOIN employee as creator ON p.created_by = creator.employee

LEFT JOIN employee as updater ON p.updated_by = updater.employee

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

Help making a multi table query

re: Help making a multi table query

Similar PostgreSQL Database bytes

/bytes/development

/bytes/it

/bytes/about