Please help with a simple join

sgtpepper

Hello everyone,

I have tried several tutorials on mysql joins and I cant seem to 'get it'. Can you please offer a solution?

It is very simple, really....but I just can get it.

I have a table that stores order data called tbl_order_item. The columns are as follows:

od_id int(10) unsigned ---> This is the order ID number
pd_id int(10) unsigned ---> This is the product ID number
od_qty int(10) unsigned ---> This is the order quanitity

All of the information about the product is stored in another table called

cert_product

The table structure looks like this

Id int(225) PRI (NULL)

pd_id int(10) unsigned 0

cert_id int(10) YES (NULL)

cat_id int(10) unsigned MUL 0 pd_name varchar(100) MUL pd_description text

pd_price decimal(7,2) 0.00

pd_qty smallint(5) unsigned 0

pd_image varchar(200) YES (NULL) pd_thumbnail varchar(200) YES (NULL) pd_date datetime 0000-00-00 00:00:00 pd_last_update datetime

All I want to do is setup a query that displays everything in the cert_product table for each product in an order. So, for every product ID that matches the orderID the information in the cert_product table should be displayed....

This seems like it should be an easy JOIN type statement....but I cant seem to grasp it.

Thanks for the help.

suntra

Well I guess tbl_order_item.pd_int refer to a row in cert_product, so tbl_order_item.pd_int = cert_product.pd_int ?

If yes...

$orderID = 1; // let's say order id is 1...
$strQuery = 'SELECT
    o.*, p.*
FROM
    tbl_order_item o
WHERE
    o.od_id=' . $orderID . '
LEFT JOIN
    cert_product p
ON
    p.pd_id=o.pd_id';
$resResult = mysql_query($strQuery);

sgtpepper

Sorry, what does o., p. represent and tbl_order_item o ??

suntra

When writing a table name, you can add a space and an alias...
So in my query, o represents the tbl_order_item table, and p, the celt_product table... I used them to simplify the code...

Let's take a simple query...

SELECT * FROM my_table

Now, let's use an alias...

SELECT mt.* FROM my_table mt

Here, we ask MySQL to use the mt alias to refer to the my_table table. So in the field name statement, we write mt. (we could have left it to "" since there's only one table...)

As soon as you have two tables, it's very useful...

Exemple :

SELECT first_very_long_table_name_that_seems_endless., second_very_long_table_name_that_seems_endless. FROM first_very_long_table_name_that_seems_endless, second_very_long_table_name_that_seems_endless WHERE second_very_long_table_name_that_seems_endless.id=first_very_long_table_name_that_seems_endless.associated_id

Don't you find that a bit too long ?!

So simply it !

SELECT t1., t2. FROM first_very_long_table_name_that_seems_endless t1, second_very_long_table_name_that_seems_endless t2 WHERE t2.id=t1.associated_id

sgtpepper

OK, I tried the query

SELECT o., p. FROM tbl_order_item o WHERE o.od_id=1022 LEFT JOIN cert_product p ON p.pd_id=o.pd_id

But got the following error....

Error Code : 1064
You have an error in your SQL syntax near 'LEFT JOIN cert_product p ON p.pd_id=o.pd_id
' at line 1
(31 ms taken)

suntra

Oh sorry, I always forget that the JOIN statement must come before the WHERE statement !!!

SELECT o., p. FROM tbl_order_item o LEFT JOIN cert_product p ON p.pd_id=o.pd_id WHERE o.od_id=1022

Tezread

here is an example for guidance. in this case I was selecting all the 'supportactivities' for a given qualityadmin:

$sql2 = "SELECT supportactivity.supportactivity_id AS supportactivity_id, supportactivity.supportactivity AS supportactivity, 
qualityadmin.quality_name AS quality_name, qualityadmin.qualityadmin_id AS qualityadmin_id 
FROM supportactivity 
LEFT JOIN supportactivityrelations ON supportactivity.supportactivity_id = supportactivityrelations.supportactivity_id 
LEFT JOIN supportactivityuser ON supportactivity.supportactivity_id = supportactivityuser.supportactivity_id 
LEFT JOIN qualityadmin ON supportactivityrelations.quality_name = qualityadmin.quality_name 
WHERE supportactivityrelations.quality_name = '$name1'";

sgtpepper

OK....so here is the problem. I get the information I need.....but there should only be 2 results....(only product ids match the order id) instead I am getting 20 instead of the 2

suntra

Which is the common field between both ?

tbl_order_item, the field pd_id corresponds to which field in celt_product ? Is it Id,
pd_id or cert_id ?

sgtpepper

pd_id

Tezread

here is an example for guidance. in this case I was selecting all the 'supportactivities' for a given qualityadmin:

$sql2 = "SELECT supportactivity.supportactivity_id AS supportactivity_id, supportactivity.supportactivity AS supportactivity, 
qualityadmin.quality_name AS quality_name, qualityadmin.qualityadmin_id AS qualityadmin_id 
FROM supportactivity 
LEFT JOIN supportactivityrelations ON supportactivity.supportactivity_id = supportactivityrelations.supportactivity_id 
LEFT JOIN supportactivityuser ON supportactivity.supportactivity_id = supportactivityuser.supportactivity_id 
LEFT JOIN qualityadmin ON supportactivityrelations.quality_name = qualityadmin.quality_name 
WHERE supportactivityrelations.quality_name = '$name1'";

suntra

First, simply try this :

SELECT o.* FROM tbl_order_item o WHERE o.od_id=1022

How many results do you get ?

sgtpepper

I get the two results I was expecting

suntra

Let's try with a simple SELECT/WHERE query...

SELECT o., p. FROM tbl_order_item o, celt_product p WHERE o.od_id=1022 AND p.pd_id=o.pd_id

sgtpepper

This produces the 20 results

suntra

In celt_product, does every pd_id have a UNIQUE value ?! Or can one specific value be used in more than one row ?

sgtpepper

They are not unique they can be used more then once....

suntra

I've made some tests and had the same problem as you...

Is celt_product.Id a unique ID ?

If yes, you should use that ID in tbl_order_item.pd_id, and not celt_product.pd_id...