Selecting from two tables in the one query...
Moderator: General Moderators
Selecting from two tables in the one query...
I have a shopping cart script and I'm setting up inventory control.
On the inventory control page, it selects the attributes added to that product. In another table, it records the attributes name and type (the types are drop down box, radio, text box and text area).
What I want to do is select the attributes added to the product and but also check what type it is. If it is a tetx box or text area, I do not want it to be retreived from the database.
I know you can select info from two tables from one query but I don't know how... Does anyone have a tutorial on how that works?
On the inventory control page, it selects the attributes added to that product. In another table, it records the attributes name and type (the types are drop down box, radio, text box and text area).
What I want to do is select the attributes added to the product and but also check what type it is. If it is a tetx box or text area, I do not want it to be retreived from the database.
I know you can select info from two tables from one query but I don't know how... Does anyone have a tutorial on how that works?
Select table1.attr1,table2.attr1 from sampletable1 table1,sampletable2 table2 where table.attr1 = 'unf';
That's the most basic form of selecting from 2 or more tables for more information, try reading up http://www.tizag.com/sqlTutorial/sqlunion.php
That's the most basic form of selecting from 2 or more tables for more information, try reading up http://www.tizag.com/sqlTutorial/sqlunion.php
Thanks sloth... Do you mind looking at my code? It's returning an error.. I've neatened the code up like that tutorial so it's easy for you to read:
select distinct(cms_listings_items_options.optionid) from cms_listings_items_options
INNER JOIN option
ON cms_listings_items_options.optionid = option.id
UNION
select cms_ecommerce_options.id from cms_ecommerce_options
INNER JOIN option
ON cms_ecommerce_options.id = option.id
where cms_listings_items_options.listingid='2' and cms_ecommerce_options.type='select'
The error:
Thanks for your input.
select distinct(cms_listings_items_options.optionid) from cms_listings_items_options
INNER JOIN option
ON cms_listings_items_options.optionid = option.id
UNION
select cms_ecommerce_options.id from cms_ecommerce_options
INNER JOIN option
ON cms_ecommerce_options.id = option.id
where cms_listings_items_options.listingid='2' and cms_ecommerce_options.type='select'
The error:
Code: Select all
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'option ON oecms_listings_items_options.optionid = option.id UNIOoption is a reserved word, see http://dev.mysql.com/doc/refman/5.0/en/ ... words.html
Please use tags for your sql code.
Please use
Code: Select all
...Cool, I changed option to options and now I've got this error:
Table 'cms.options' doesn't exist
Is options supposed to be a table? I thought it was just the name for the join...
Thanks again for your help!
Table 'cms.options' doesn't exist
Is options supposed to be a table? I thought it was just the name for the join...
Thanks again for your help!
Code: Select all
select distinct(cms_listings_items_options.optionid) from cms_listings_items_options
INNER JOIN options
ON cms_listings_items_options.optionid = options.id
UNION
select cms_ecommerce_options.id from cms_ecommerce_options
INNER JOIN options
ON cms_ecommerce_options.id = options.id
where cms_listings_items_options.listingid='2' and cms_ecommerce_options.type='select' What made me think that? I have no idea. I'm just guessing from the answer Sloth gave me. I'll try explain what I'm trying to acheive again:
I'm trying to select from two tables in the one query.
1st Table: I need to select the attributes that are linked to a specific listing. The table name is cms_listings_items_options
2nd Table: I need to select the type of attribute (the different types are select, radio, text box and text area) from the main attributes table which is called cms_ecommerce_options. The reason for this is I don't want it to display the attributes that do not have the type field set to select.
So basically, in the one query I need to do the two above actions. I was hoping that UNION code would do the trick... Maybe not..
The "real" tables that I thought were the real tables were cms_listings_items_options and cms_ecommerce_options. From what I think you're telling me is that there are three tables that need to be selected...
Am I making sense?
I'm trying to select from two tables in the one query.
1st Table: I need to select the attributes that are linked to a specific listing. The table name is cms_listings_items_options
2nd Table: I need to select the type of attribute (the different types are select, radio, text box and text area) from the main attributes table which is called cms_ecommerce_options. The reason for this is I don't want it to display the attributes that do not have the type field set to select.
So basically, in the one query I need to do the two above actions. I was hoping that UNION code would do the trick... Maybe not..
The "real" tables that I thought were the real tables were cms_listings_items_options and cms_ecommerce_options. From what I think you're telling me is that there are three tables that need to be selected...
Am I making sense?
- dibyendrah
- Forum Contributor
- Posts: 491
- Joined: Wed Oct 19, 2005 5:14 am
- Location: Nepal
- Contact:
Here are the table structures and some sample data. The optionid from cms_listings_items_options is what identifies itself from the cms_ecommerce_option table.
If you need anymore explanation, feel free to ask.
If you need anymore explanation, feel free to ask.
Code: Select all
CREATE TABLE `cms_ecommerce_options` (
`id` int(10) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`type` enum('select','radio','textbox','textarea') NOT NULL default 'select',
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
INSERT INTO `cms_ecommerce_options` VALUES (1, 'Colour', 'select');
INSERT INTO `cms_ecommerce_options` VALUES (2, 'Size', 'select');
INSERT INTO `cms_ecommerce_options` VALUES (3, 'Length', 'select');
INSERT INTO `cms_ecommerce_options` VALUES (4, 'Width', 'select');
INSERT INTO `cms_ecommerce_options` VALUES (6, 'Comments', 'textbox');
CREATE TABLE `cms_listings_items_options` (
`id` int(10) NOT NULL auto_increment,
`listingid` int(10) NOT NULL default '0',
`optionid` int(10) NOT NULL default '0',
`valueid` int(10) NOT NULL default '0',
`stock` int(10) NOT NULL default '0',
`weight` double NOT NULL default '0',
`weight_prefix` enum('+','-') NOT NULL default '+',
`price` double NOT NULL default '0',
`price_prefix` enum('+','-') NOT NULL default '+',
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
INSERT INTO `cms_listings_items_options` VALUES (1, 2, 6, 0, 0, 0, '+', 0, '+');
INSERT INTO `cms_listings_items_options` VALUES (2, 1, 1, 1, 0, 200, '+', 1000, '+');
INSERT INTO `cms_listings_items_options` VALUES (3, 1, 1, 2, 0, 0, '+', 0, '+');
INSERT INTO `cms_listings_items_options` VALUES (4, 1, 1, 3, 0, 0, '+', 0, '+');
INSERT INTO `cms_listings_items_options` VALUES (5, 1, 1, 4, 0, 0, '+', 0, '+');
INSERT INTO `cms_listings_items_options` VALUES (6, 1, 3, 8, 0, 0, '+', 0, '+');
INSERT INTO `cms_listings_items_options` VALUES (7, 1, 3, 9, 0, 0, '+', 0, '+');
INSERT INTO `cms_listings_items_options` VALUES (8, 1, 2, 5, 0, 0, '+', 0, '+');
INSERT INTO `cms_listings_items_options` VALUES (10, 1, 2, 6, 0, 0, '+', 0, '+');
INSERT INTO `cms_listings_items_options` VALUES (11, 1, 2, 7, 0, 0, '+', 0, '+');
INSERT INTO `cms_listings_items_options` VALUES (14, 1, 4, 11, 0, 0, '+', 0, '+');I'm not sure I understand you. Let's try it step by step.
The basic query could beand the result isNow the optionid refers to the field id in cms_ecommerce_options, if the value optionid is equal to id the records "belong" together. That's what JOIN ... ON does.You want to make sure eo.type (also) equals select, then add it to the queryDone.
The basic query could be
Code: Select all
SELECT
li.id, li.optionid
FROM
cms_listings_items_options AS li
WHERE
li.listingid=1Code: Select all
id optionid
2 1
3 1
4 1
5 1
6 3
7 3
8 2
10 2
11 2
14 4Code: Select all
SELECT
li.id, li.optionid,
eo.name,eo.type
FROM
cms_listings_items_options AS li
JOIN
cms_ecommerce_options AS eo
ON
li.optionid=eo.id
WHERE
li.listingid=1Code: Select all
id optionid name type
2 1 Colour select
3 1 Colour select
4 1 Colour select
5 1 Colour select
8 2 Size select
10 2 Size select
11 2 Size select
6 3 Length select
7 3 Length select
14 4 Width selectCode: Select all
SELECT
li.id, li.optionid,
eo.name,eo.type
FROM
cms_listings_items_options AS li
JOIN
cms_ecommerce_options AS eo
ON
li.optionid=eo.id
WHERE
li.listingid=1
AND eo.type='select'