selcting set values from multiple tables in a single query
Posted: Wed Sep 29, 2010 12:14 pm
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
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