How can I call sub-query?

[rejoice]

How can I call sub-query in MYSQL? For example:

SELECT * FROM pr_groups WHERE pr_groups.group_id=(SELECT groups_id FROM pr_items)

Besides, does mySQL allow creation of query file like MS-Access, so that I can call the query file from another query statement. For example:

SELECT * FROM query_file_name

 

[swampBoogie]

How can I call sub-query in MYSQL?

You can't. It is not supported in any released version of Mysql. (Only alpha-test versions.)

SELECT pr_groups.* FROM pr_groups join pr_items
on pr_groups.group_id = pr_items.groups_id

should yield the same result (as you use = in the comparison).

does mySQL allow creation of query file like MS-Access

No.

 

[sleipnir214]

rejoice:
The type of subquery you are trying to use can also be written as a join of two tables.

See this page for more information:

http://www.mysql.com/doc/en/Rewriting_subqueries.html

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[swampBoogie]

What Mysql claims on that page is not true.

create table t1(c1 int);
insert into t1 values(1);
insert into t1 values(1);
create table t2(c1 int);
insert into t2 values(1); 
insert into t2 values(1); 

select * from t1 where c1 in (select c1 from t2);

select t1.* from t1 join t2 on t1.c1 = t2.c1;

These two select statement will not give the same result.

 

[r937]

swampboogie, that's harsh

true, but harsh

rudy

http://r937.com/

SQL Consulting

 

[sleipnir214]

swampBoogie:

On the page titled " Rewriting Subqueries for Earlier MySQL Versions", the MySQL online manual states, "It is often possible to rewrite a query without a subquery[/b]"

So you're saying that isn't true?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[r937]

no, he's just giving a counterexample

"It is often possible to rewrite a query without a subquery"

often, but not always

the page cited seemed to imply that those two queries were equivalent

they aren't always

it depends on the data





rudy

http://r937.com/

SQL Consulting

 

[sleipnir214]

The real gotcha, as demonstrated by swampBoogie's example, is repetition in the values in your subqueries. I think that if your subquery values are unique, the two should be equivalent.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[rejoice]

Thanks, but harsh :-(