Hi,
I have two tables, one called 'products' and a second called 'shipping' and a third called 'images' It is for a cart application and I am trying to reduce the amount of code, database queries and actuall processing etc.
here is what the three tables look like:
[text]
Product:
+------------------+--------------+-
| Field | Type |
+------------------+--------------+-
| id | int(11) |
| name | varchar(32) |
| description | text |
| long_description | text |
| photo_id | int(11) |
| price | decimal(6,2) |
| cat_id | int(11) |
| sub_cat_id | int(11) |
| shipping | int(11) |
| rank | int(11) |
+------------------+--------------+-[/text]
[text]
Shipping:
+-------+--------------+
| Field | Type |
+-------+--------------+
| id | int(11) |
| type | varchar(10) |
| cost | decimal(6,2) |
+-------+--------------+
[/text]
[text]
Photograph
+---------------+--------------+
| Field | Type |
+---------------+--------------+
| id | int(11) |
| filename | varchar(64) |
| type | varchar(10) |
| size | int(11) |
| dimensions_id | int(11) |
| caption | varchar(255) |
+---------------+--------------+[/text]
I have a page that displays all products in a category, along with a small image and a short description of the product.
the user clicks on a product and is taken to a second page which displays full details of the product along with an image, longer description and other product attributes.
This is the first time I have really come across a need for using 'Join' or 'Union' and I am a little uneasy using this as although I want to reduce the amount of data traffic , the only way I know at the moment is creating a query to extract the product data, then use the shipping ID from this to find the cost of shipping, then use the photo_id from the original query to find the photograph name.
I know there are ways of being able to get all this data in a single query but I have no idea how, please can someone point me in the right direction or provide a link to a page which can help?
Many thanks
jimmy
selcting set values from multiple tables in a single query
Moderator: General Moderators
Re: selcting set values from multiple tables in a single que
Assuming that 'shipping' in the Product table is the 'id' from shipping:
[text]SELECT p.id, p.name, p.description, p.long_description, p.price, p.sub_cat_id, p.rank,
s.type, s.cost, f.filename, f.type, f.size, f.dimensions_id, f.caption
FROM Product AS p
LEFT JOIN Shipping AS s ON s.id = p.shipping
LEFT JOIN Photograph AS f ON f.id = p.photo_id
WHERE p.cat_id = (WHATEVER)
[/text]
Note, for your two queries, you can mix and match the parts you need, (ie, only get long description on full details), but that should be a good base to go off of.
IMO, when you are mixing tables or doing subqueries, it is best to use the aliases to make sure which field is which.
-Greg
[text]SELECT p.id, p.name, p.description, p.long_description, p.price, p.sub_cat_id, p.rank,
s.type, s.cost, f.filename, f.type, f.size, f.dimensions_id, f.caption
FROM Product AS p
LEFT JOIN Shipping AS s ON s.id = p.shipping
LEFT JOIN Photograph AS f ON f.id = p.photo_id
WHERE p.cat_id = (WHATEVER)
[/text]
Note, for your two queries, you can mix and match the parts you need, (ie, only get long description on full details), but that should be a good base to go off of.
IMO, when you are mixing tables or doing subqueries, it is best to use the aliases to make sure which field is which.
-Greg
Re: selcting set values from multiple tables in a single que
Greg,
That is fantastic, worked straight away, and it is VERY logical in its construction, wil be using this type of query in future.
Jimmy
That is fantastic, worked straight away, and it is VERY logical in its construction, wil be using this type of query in future.
Jimmy
Re: selcting set values from multiple tables in a single que
If you are new to joins, remember, the LEFT JOIN gets used where you want rows even if there isn't matching information in the Joined table. (ie, get all products, but if there are no images, their fields will come back NULL)
Use a INNER JOIN (or just JOIN) where there needs to be something in both tables. (ie, you wouldn't get products if there was no images)
For small record sets this works well. When you get to larger record sets and multiple joins, then it may be worth getting back the main data first, then running the second query.
-Greg
Use a INNER JOIN (or just JOIN) where there needs to be something in both tables. (ie, you wouldn't get products if there was no images)
For small record sets this works well. When you get to larger record sets and multiple joins, then it may be worth getting back the main data first, then running the second query.
-Greg