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?

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; 

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

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`

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.

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