Problems writing a query for MySQL database

Problems writing a query for MySQL database

I have two tables in my SQL database:

mysql> select *from crop;  +------+-----------+----------+  | no   | name      | type     |  +------+-----------+----------+  | 1    | pineapple | fruits   |  | 2    | wheat     | mainFood |  | 1    | apple     | fruits   |  | 2    | corn      | main     |  | 3    | rose      | flower   |  | 2    | wheat     | main     |  | 2    | maize     | main     |  | 1    | drydates  | fruits   |  +------+-----------+----------+    mysql> select *from enviornment;  +---------+------------+----------+------+  | climate | irrigation | soil     | no   |  +---------+------------+----------+------+  | humid   | medium     | alluvial | 2    |  | humid   | medium     | black    | 1    |  | humid   | medium     | red      | 1    |  | sunny   | low        | black    | 1    |  | sunny   | medium     | alluvial | 1    |  | wet     | high       | red      | 2    |  | humid   | low        | red      | 3    |  +---------+------------+----------+------+  

I want to get the name and type fields from the crop table, based on climate, soil, and irrigation.

I have written my query in the following way:

mysql> select T.name from((select name from crop)as T and (select no from envior  nment where climate like wet)as U)where T.no=U.no;  

But when I try to execute it, I get the following error:

ERROR 1064 (42000): 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 'and (select no from enviornment where climate like wet)as U)where T.no=U.no' at line 1

Can anyone tell me how to re-write my query to avoid this error?

Answer by Sean Adkinson for Problems writing a query for MySQL database

select T.name   from (select name from crop) as T   inner join (select no from enviornment where climate like wet) as U  on T.no = U.no  

Answer by Laimoncijus for Problems writing a query for MySQL database

You could do the same without using subselects, which would be faster:

SELECT `T`.`name`   FROM `enviornment` AS `U`       , `crop` AS `T`  WHERE `U`.`climate` LIKE 'wet'      AND `U`.`no` = `T`.`no`  

Answer by crunchdog for Problems writing a query for MySQL database

You cannot use AND to construct query-results, it's a logical operator. You can get all name, type, climate, soil and irrigation combinations with:

select c.name, c.type, e.climate, e.soil, e.irrigation  from crop c, environment e  where c.no = e.no;   

Answer by Guffa for Problems writing a query for MySQL database

You should use comma between tables in the from clause, not and. You have forgotten the apostrophes around the string 'wet'.

There is no point in selecting from subselects, you should just select from the tables directly:

select    T.name, T.type  from    crop as T,    enviornment as U  where    T.no = U.no and U.climate = 'wet'  

Nowadays a join is commonly done using the join command:

select    T.name, T.type  from    crop as T,    inner join enviornment as U on T.no = U.no  where    U.climate = 'wet'  

Note: Your table name enviornment is spelled wrong, it should be environment.

Answer by wired00 for Problems writing a query for MySQL database

user711934, although its nice having people show how to rewrite queries i suggest you do some more tutorials or buy a book etc on SQL queries specifically relating to joins to learn the fundamentals. You shouldn't rely on sub queries they are less efficient.

i suggest doing these tutorials http://www.sql-tutorial.net/

specifically go through these join examples http://www.sql-tutorial.net/SQL-JOIN.asp

http://beginner-sql-tutorial.com/sql-query-tuning.htm

I hope that helps

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72