Thanks for the Reply Geoffery. The join key I am getting from the select from another table (iris_subset)

DELIMITER //
CREATE PROCEDURE GetSameRecords()
READS SQL DATA
BEGIN
DECLARE next_subset VARCHAR(75);
select subset INTO next_subset FROM iris_subset where id = 1;
set @s = “select count(*) from iris_setosa inner join iris_versicolor using (next_subset)”;
PREPARE stmt1 FROM @s;

EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;
END //
DELIMITER ;

but when I call that procedure, I get the error:

ERROR 1054 (42S22): Unknown column ‘next_subset’ in ‘from clause’

Thanks.

Hi Shahid,

Let me briefly explain “join..using” syntax. It is a simplified version of full join syntax. Suppose we have:

Because the join condition uses columns with the same name from both tables it can be simplified to:

Thus inside ‘using()’ you can place only column or columns that are common for both tables. It can’t be a variable like in your case. See http://dev.mysql.com/doc/refman/5.1/en/join.html for more details.

Thanks,
Piotr

Thanks Geoffery. I have the following situation, do you think it is because of ICE and I need IEE.

mysql> select * from iris_subset;
+———+—————————————————————————-+—————+
| ID   | SUBSET                             | RED_FLAG |
+———+—————————————————————————-+—————+
|  1 | sepal_length                         | N     |
|  2 | sepal_width                           | N     |
|  3 | petal_length                         | N     |
|  4 | petal_width                           | N     |
|  5 | sepal_length,sepal_width                 | N     |
|  6 | sepal_length,petal_length                 | N     |
|  7 | sepal_length,petal_width                 | N     |
|  8 | sepal_width,petal_length                 | N     |
|  9 | sepal_width,petal_width                   | N     |
|  10 | petal_length,petal_width                 | N     |
|  11 | sepal_length,sepal_width,petal_length         | N     |
|  12 | sepal_length,sepal_width,petal_width         | N     |
|  13 | sepal_length,petal_length,petal_width         | N     |
|  14 | sepal_width,petal_length,petal_width         | N     |
|  15 | sepal_length,sepal_width,petal_length,petal_width | N     |
+———+—————————————————————————-+—————+
15 rows in set (0.00 sec)

mysql> update iris_subset set red_flag = ‘S’ where subset like ‘%sepal_width,petal_width%’;
ERROR 1031 (HY000): Table storage engine for ‘iris_subset’ doesn’t have this option

Thanks.

Yes, DML functionality (insert, update, delete) is available in IEE only.

Piotr