CODEWITHSUNDEEP
c#sql
dave1993
dave1993

Reputation: 7

Where did I make a mistake in my SQL statement?

I want to get data from database by filtering 4 columns. First column name is Money (amount of money) and the second is Currency. There are two additional columns in the db with the same definitions for example: Money2 and Currency2.

So I want to get all records where Money is larger than 100 and Currency is USD, but additionally I want to get all data where Money2 is larger than 200 and Currency2 is EUR.

I have the following query, but unfortunately it doesn't give results from the second query.

Code:

SELECT * 
FROM DB 
WHERE (Money > 100 AND Currency = 'USD' OR Money2 > 200 AND Currency2 = 'EUR')

Where did I make a mistake?

Upvotes: 0

Views: 84

Answers (4)

WiSeeker
WiSeeker

Reputation: 842

Have you tried a UNION? This will allow you to run each query independently and help you narrow down the issue.

select * from db where Money > 100 and currency = 'USD'
UN ION
select * from db where Money2 > 200 AND Currency2 = 'EUR'

(Note that space in UNION is intentional; without it SO doesn't want to post this answer)

Upvotes: 0

Yuval Perelman
Yuval Perelman

Reputation: 4819

After FROM needs to be a name of a table, DB doesn't sound like a name of a table

USE DB
SELECT * 
FROM --TABLE NAME-- 
WHERE (Money > 100 AND Currency = 'USD') OR (Money2 > 200 AND Currency2 = 'EUR')

Upvotes: 1

Alan Hadsell
Alan Hadsell

Reputation: 470

SELECT * FROM DB 
WHERE (Money > 100 AND Currency = 'USD') OR (Money2 > 200 AND Currency2 = 'EUR')

Upvotes: 1

JamieSee
JamieSee

Reputation: 13030

You may be running into an operator precedence issue.

Try forcing the evaluation order using parentheses. Also use .0 to avoid possible unintended conversions to int.

SELECT * FROM DB 
WHERE ((Money > 100.0 AND Currency = 'USD') OR (Money2 > 200.0 AND Currency2 = 'EUR'))

Upvotes: 5

Related Questions