CODEWITHSUNDEEP
mysqlsqlselect
swapnesh
swapnesh

Reputation: 26732

logical error in mysql query while selecting data

Table pc -

 code   model   speed   ram hd  cd  price
 1  1232    500 64  5.0 12x 600.0000
 10 1260    500 32  10.0    12x 350.0000
 11 1233    900 128 40.0    40x 980.0000
 12 1233    800 128 20.0    50x 970.0000
 2  1121    750 128 14.0    40x 850.0000
 3  1233    500 64  5.0 12x 600.0000
 4  1121    600 128 14.0    40x 850.0000
 5  1121    600 128 8.0 40x 850.0000
 6  1233    750 128 20.0    50x 950.0000
 7  1232    500 32  10.0    12x 400.0000
 8  1232    450 64  8.0 24x 350.0000
 9  1232    450 32  10.0    24x 350.0000

Desired output -

model   speed   hd
1232    450 10.0
1232    450 8.0
1232    500 10.0
1260    500 10.0

Query 1 -

SELECT model, speed, hd
FROM pc
WHERE cd = '12x' AND price < 600
OR
cd = '24x' AND price < 600

Query 2 - 

SELECT model, speed, hd
FROM pc
WHERE cd = '12x' OR cd = '24x' 
AND price < 600

Query 1 is definitely working correctly, however when i tried to reduce the query to use price at once only, it is not showing the correct result..let me know what I am missing in the logic.

Find the model number, speed and hard drive capacity of the PCs having 12x CD and prices less than $600 or having 24x CD and prices less than $600.

Upvotes: 2

Views: 708

Answers (3)

narendra
narendra

Reputation: 21

In your table may contain duplicate rows or coloumns try by using group by clause as shown below where you will get the soloution and let me know the output after trying these thanks...

SELECT model, speed, hd
FROM PC 
WHERE cd IN ('12x','24x') AND price < 600
group by Model,speed,hd

Upvotes: 2

sgeddes
sgeddes

Reputation: 62831

Try using IN:

SELECT model, speed, hd
FROM PC 
WHERE cd IN ('12x','24x') AND price < 600

Good luck.

Upvotes: 1

lc.
lc.

Reputation: 116458

Since AND comes before OR, your query is being interpreted as:

WHERE (cd = '12x') OR ((cd = '24x') AND (price < 600))

Or, in words: All PCs having 12x CD, or PCs < $600 having 24x CD

You need to use parentheses to specify order of operations:

WHERE (cd = '12x' OR cd = '24x') AND price < 600

Or, you can use IN:

WHERE cd IN ('12x', '24x') AND price < 600

See Also: http://dev.mysql.com/doc/refman/5.5/en/operator-precedence.html

Upvotes: 3

Related Questions