CODEWITHSUNDEEP
mysqlsql
user711934
user711934

Reputation: 3

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?

Upvotes: 0

Views: 170

Answers (5)

wired00
wired00

Reputation: 14508

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

Upvotes: 0

Guffa
Guffa

Reputation: 700840

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.

Upvotes: 0

crunchdog
crunchdog

Reputation: 13478

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;

Upvotes: 1

Nobwyn
Nobwyn

Reputation: 8685

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`

Upvotes: 0

Sean Adkinson
Sean Adkinson

Reputation: 8615

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

Upvotes: 1

Related Questions