subqueries with SQLyog?

[floflo]

select * from `table1` where `num` in (select `num` from `table2` where `unit` = “50”);

I am unable to run the above query in SQLyog – I understand SQLyog runs SQL v 4.1 which I thought supported subqueries. Is there a problem with my syntax or does SQLyog not support this?

[peterlaursen]

sqlyog does not run any specific Mysql version. Sqlyog is a CLIENT that connects to a MySQL SERVER and the SERVER runs some specific MySQL version. If you execute 'something' from the SQL-pane in sqlyog, sqlyog does nothing but passing that 'something' on to the MySQL server. Any error-masages that you get back are error messages sent by the server. Sqlyog does not parse at all!

According to the mysql documentation the basic subquery construction is something like

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

A subquery is a SELECT statement inside another statement.

A subquery's outer statement can be any one of: SELECT, INSERT, UPDATE, DELETE, SET, or DO.

With my Music Library this statement

select * from music.artistview where artist = (select artist from music.mp3_files where album = 'Music spoken here' group by kunstner);

runs from sqlyog and result is correct. It returns all the albums that are that I have in the library with this artist.

Inner statement returns “John McLaughlin”

Outer statement is thus equal to

select * from music.artistview where artist = 'John McLaughlin';

mp3_files is the music libray, artistview is a VIEW based on mp3_files (but could be an ordinary/derived table as well!)

*Music spoken Here* is an album by john McLaughlin.

I don't have better data to illustrate it with ….

[peterlaursen]

and .. is unit a string-type ?

[peterlaursen]

to test it:

1) does the inner statement run ?

2) does the in

[peterlaursen]

I am sorry .. something went wrong with my mouse

is it just

select * from `table1` where `num` in (select `num` from `table2` where `unit` = “50”);

that should be

select * from `table1` where `num` = (select `num` from `table2` where `unit` = “50”);

If the inner statement runs and returns a single value the latter should run.

But I can't see your data – so what it returns …. 🙄

[Ritesh]

What is the error you are getting?

99.99% chances are that your query is wrong.

[McDuck]

I have yhe same problem.

I have MySQL version 4.0.0 and I want to execute the following query:

SELECT a.* FROM al_assets a

INNER JOIN al_asset_details b on a.asset_id = b.asset_id

WHERE

b.name=”color_code”

AND b.value = “0308”

AND a.asset_id in (

SELECT a.asset_id from al_assets a

INNER JOIN al_asset_details b on a.asset_id = b.asset_id

WHERE b.name=”product_code” AND b.value = “571432”)

and I get error message:

Error Code : 1064

You have an error in your SQL syntax near 'select `a.asset_id` from `al_assets` a inner join al_asset_details b on a.asset_' at line 6

Why does the query not work? Anyone?

I have tried to replace the word IN with = sign but, that results in the same message

[peterlaursen]

MySQL 4.0.0. ???????????????????

if you read the error message and study the beginning of you SQL

“SELECT a.* FROM al_assets a ..”  I guess you are missing a ” . ” ??

[Author]

Posts