CODEWITHSUNDEEP
mysqlsqlselect
Sadeq Shajary
Sadeq Shajary

Reputation: 351

sql SELECT query for 3 tables

I have 3 tables:
1. products(product_id,name)
2. orders(id,order_id,product_id)
3. factors(id,order_id,date)

I want to retrieve product names(products.name) where have similar order_id on a date in two last tables. I use this query for this purpose: 

select products.name
    from products

    WHERE products.product_id ~IN 
    (
    SELECT distinct orders.product_id FROM orders WHERE
    order_id IN (select order_id FROM factors WHERE 
    factors.datex ='2017-04-29') GROUP BY product_id
    )

but no result. where is my mistake? how can I resolve that? thanks

Upvotes: 2

Views: 67

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Your query should be fine. I am rewriting it to make a few changes to the structure, but not the logic (this makes it easier for me to understand the query):

select p.name
from products p
where p.product_id in (select o.product_id
                       from orders o
                       where o.order_id in (select f.order_id
                                            from factors f
                                            where f.datex = '2017-04-29'
                                           ) 
                      ) ;

Notes on the changes:

  • When using multiple tables in a query, always qualify the column names.
  • Use table aliases. They make queries easier to write and to read.
  • SELECT DISTINCT and GROUP BY are unnecessary in IN subqueries. The logic of IN already handles (i.e. ignores) duplicates. And by explicitly including the operations, you run the risk of a less efficient query plan.

Why might your query not work?

  • factors.datex has a time component. If so, then this will work date(f.datex) = '2017-04-29'.
  • There are no factors on that date.
  • There are no orders that match factors on that date.
  • There are no products in the orders that match the factors on that date.

Upvotes: 2

Chirag Jain
Chirag Jain

Reputation: 628

In factors table column name is date so it should be - 

 factors.date ='2017-04-29'

You have written -

factors.datex ='2017-04-29'

Upvotes: 1

Related Questions