473,387 Members | 1,779 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

Help making a multi table query

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
1 3418
foobar
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

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

Similar topics

5
by: Steve | last post by:
I'm trying to do a multi table query that displays all the info I need in one array, and I'm having problems figuring out how to do it. The two tables are product and vendor. A vendor can be a...
5
by: David Logan | last post by:
Hello, I am trying to construct a query across 5 tables but primarily 3 tables. Plan, Provider, ProviderLocation are the three primary tables the other tables are lookup tables for values the...
9
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my...
3
by: usenetaccount | last post by:
In a newly created test app, to maximize client performance I tried to make two SOAP method calls in tandem (the soap methods execute some specified query), as each call includes a large amount of...
4
by: yanjie.ma | last post by:
Hi, I've got a two part question on table and form design (sorry for the length but it takes a bit to explain). Our sales department uses a look-up table to help the them select the best...
0
by: gunimpi | last post by:
http://www.vbforums.com/showthread.php?p=2745431#post2745431 ******************************************************** VB6 OR VBA & Webbrowser DOM Tiny $50 Mini Project Programmer help wanted...
47
by: Jo | last post by:
Hi there, I'm Jo and it's the first time I've posted here. I'm in process of creating a database at work and have come a little unstuck.....I'm a bit of a novice and wondered if anyone could...
9
by: Sam Lambson | last post by:
Hi, This is similar to another post, because I still haven't gotten to the bottom of it. I have a back end database on a shared drive. Each (of 4) user has a front end file on their own C:...
3
JodiPhillips
by: JodiPhillips | last post by:
Hello everyone, there are many questions and answers relating to moving items between two listboxes here and on the net in general, however, none answer my specific problem. I have two listboxes...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.