CODEWITHSUNDEEP
mysqlsql
Guenyeu Tan
Guenyeu Tan

Reputation: 25

Syntax for nested queries in mysql

I have 3 tables:

  1. stocktake
  2. supplier
  3. accessories

I want to select total_inventories,accessory_id,supplier_id,conpany_name,phone_no this 5 things in this three table and the total_inventorieshave to <100

and I have to use Nested Query or either standard or correlated sub-query

that is what I have done

SELECT total_inventories,accessory_id,supplier_id,conpany_name,phone_no
FROM stocktake,supplier,accessories
WHERE stocktake.accessory_id=accessory.accessory_id,accessory.supplier_id=supplier.supplier_id,
accessory_id IN (SELECT accessory_id FROM stocktake WHERE total_inventories <100)

but it got an error 

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 'accessory.supplier_id=supplier.supplier_id,
accessory_id in (select accessory_i' at line 3

How can I fix it?

Upvotes: 0

Views: 47

Answers (2)

Nameless
Nameless

Reputation: 523

You cannot use comma in where operator try. You can also refer join operation in sql.

FROM stocktake,supplier,accessories
WHERE stocktake.accessory_id=accessory.accessory_id and accessory.supplier_id=supplier.supplier_id and
accessory_id IN (SELECT accessory_id FROM stocktake WHERE total_inventories <100)

The query can be optimized as:

FROM stocktake,supplier,accessories
WHERE stocktake.accessory_id=accessory.accessory_id and accessory.supplier_id=supplier.supplier_id and
stocktake.total_inventories <100

We would not need subquery, we can directly filter total_inventories values as we have already joined three tables.

Upvotes: 1

zealous
zealous

Reputation: 7503

few things to mention in your code

  1. you are using accessories table but using accessory.supplier_id. so you need to use correct the table name.

  2. always use explicit join instead of implicit joins.

  3. always use alias for the tables for more readability.

  4. use exists instead of IN

FROM stocktake s

join accessories a
on s.accessory_id = a.accessory_id

join supplier su
on a.supplier_id = su.supplier_id

WHERE a.accessory_id IN (SELECT accessory_id FROM stocktake WHERE total_inventories < 100)

In your solution you can replace IN with exists as following

WHERE a.accessory_id exists 
(
  SELECT 
    accessory_id 
  FROM stocktake s 
  WHERE a.accessory_id = s.accessory_id
  and total_inventories < 100
)

Upvotes: 2

Related Questions