Page 1 of 1

Selecting from two tables in the one query...

Posted: Sun Dec 10, 2006 10:56 pm
by Mr Tech
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?

Posted: Sun Dec 10, 2006 11:31 pm
by Sloth
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

Posted: Tue Dec 12, 2006 12:49 am
by Mr Tech
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:

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 UNIO
Thanks for your input.

Posted: Tue Dec 12, 2006 1:31 am
by volka
option is a reserved word, see http://dev.mysql.com/doc/refman/5.0/en/ ... words.html

Please use

Code: Select all

...
tags for your sql code.

Posted: Tue Dec 12, 2006 1:39 am
by Mr Tech
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!

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' 

Posted: Tue Dec 12, 2006 1:41 am
by volka
Mr Tech wrote:Is options supposed to be a table?
Yes.
Mr Tech wrote:I thought it was just the name for the join...
What made you think that? (say for a moment it were, where in your query would the "real" table/source hide?)

Posted: Tue Dec 12, 2006 2:00 am
by Mr Tech
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?

Posted: Tue Dec 12, 2006 3:09 am
by dibyendrah
Table schema of tables will be helpful for debugging your SQL query.

Posted: Tue Dec 12, 2006 4:34 am
by Mr Tech
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.

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, '+');

Posted: Tue Dec 12, 2006 6:12 am
by volka
I'm not sure I understand you. Let's try it step by step.

The basic query could be

Code: Select all

SELECT
      li.id, li.optionid
FROM
    cms_listings_items_options AS li
WHERE
     li.listingid=1
and the result is

Code: Select all

id   optionid
2       1
3       1
4       1
5       1
6       3
7       3
8       2
10      2
11      2
14      4
Now 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.

Code: 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

Code: 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       select
You want to make sure eo.type (also) equals select, then add it to the query

Code: 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'
Done.

Posted: Tue Dec 12, 2006 4:28 pm
by Mr Tech
That's exactly what I needed! I'll give it a test today and elt you know how I go.

Thanks mate :D

Posted: Tue Dec 12, 2006 9:05 pm
by Mr Tech
It worked! You're a legend! :D :D