CODEWITHSUNDEEP
mysqlsql
Mesho
Mesho

Reputation: 45

how to make query select records and one of the column has two values and between two tables

i have two tables

product table

product_key      product_name         product_size       product_type
14913310         Snickers             32G                chocolate
5000159459228    Twix                 50G                chocolate

price table and it is weak entity

product_key      store_id             date               price
14913310         1                    2014-05-14         1.5
14913310         1                    2014-05-15         1.5
14913310         2                    2014-05-15         1.5
14913310         2                    2014-05-14         2.0
5000159459228    1                    2014-05-15         2.5

and i have inputs from user which is two product_key and store_id and date like this

product_key=14913310

product_key=5000159459228

date=2014-05-15

store_id=1

and i need output (product_key, product_name, product_size, price) like this

product_key      product_name         product_size       price
14913310         Snickers             32G                1.5
5000159459228    Twix                 50G                2.5

i try this query 

SELECT price.product_key, product.product_name, product.product_size, price.price FROM price INNER JOIN product ON price.product_key=product.product_key AND price.date='$date' AND price.store_id='$store_id' AND product.product_key='$pk1' OR product.product_key='$pk2'

and it gives me wrong result

can any one help?

Upvotes: 2

Views: 56

Answers (2)

neelsg
neelsg

Reputation: 4842

Try the following:

SELECT
    price.product_key,
    product.product_name,
    product.product_size,
    price.price
FROM
    price
    INNER JOIN product ON
        price.product_key = product.product_key
WHERE
    price.date='$date'
    AND price.store_id='$store_id'
    AND (
        product.product_key='$pk1'
        OR product.product_key='$pk2'
        )

The last OR was not in brackets, so it would return everything for $pk2 regardless of the other criteria. It helps when you write out your statements in a nice readable format to spot logic errors like these

Upvotes: 1

Lamak
Lamak

Reputation: 70658

SELECT  price.product_key, 
        product.product_name, 
        product.product_size, 
        price.price 
FROM price a
INNER JOIN product as 
    ON price.product_key = product.product_key 
WHERE price.date = '$date' 
AND price.store_id = '$store_id' 
AND (product.product_key = '$pk1' 
     OR product.product_key = '$pk2') --< here was the problem

Upvotes: 0

Related Questions