CODEWITHSUNDEEP
phpmysqlsqlselect
Leon Armstrong
Leon Armstrong

Reputation: 1303

How to select data with condition

I am trying to retrieve all row of data within some date range with only 1 condition which is only retrieve first row of type 'balance' , i think my question should explain better with data sample.

Here is my table statement

| date | type     | credit | debit | balance |
|   a  | balance  |        |       |  100.00 |
|   a  | credit   | 50.00  |       |         |
|   a  | credit   | 50.00  |       |         |
|   a  | debit    |        | 50.00 |         |
|   b  | balance  |        |       |  50.00  |
|   b  | credit   | 50.00  |       |         |
|   b  | credit   | 50.00  |       |         |
|   b  | credit   | 50.00  |       |         |
|   b  | balance  |        |       | -100.00 |
|   c  | debit    |        | 250.00|         |

This is what i trying to do (expected result)

I need to perform a query which return all row from date a to c but only first row with type balance

| date | type     | credit | debit | balance |
|   a  | balance  |        |       |  100.00 |
|   a  | credit   | 50.00  |       |         |
|   a  | credit   | 50.00  |       |         |
|   a  | debit    |        | 50.00 |         |
|   b  | credit   | 50.00  |       |         |
|   b  | credit   | 50.00  |       |         |
|   b  | credit   | 50.00  |       |         |
|   c  | debit    |        | 250.00|         |

What i know here is i can achieve by doing 2 different query 

SELECT * FROM statement WHERE date >= a and date <= c and type = 'balance' ORDER BY date ASC LIMIT 1
SELECT * FROM statement WHERE date >= a and date <= c and type != 'balance' ORDER BY date ASC

Here is my question , For some reason i need to done this with 1 single query , How do i achieve this result by one query.

Upvotes: 1

Views: 83

Answers (3)

R R
R R

Reputation: 2956

I dont think you need to give both type = 'balance' and type != 'balance' condition.it doesnot make any sense.the following query will fetch the same records.

SELECT * 
FROM statement 
WHERE date >= a and date <= c  ORDER BY type,date ASC

Upvotes: 0

Linga
Linga

Reputation: 10573

Use Union with distinct

SELECT * FROM statement WHERE date >= a and date <= c and type = 'balance' ORDER BY date ASC LIMIT 1
UNION DISTINCT
SELECT * FROM statement WHERE date >= a and date <= c and type != 'balance' ORDER BY date ASC

Upvotes: 1

Nelly
Nelly

Reputation: 522

Did you try a UNION?

SELECT * FROM statement WHERE date >= a and date <= c and type = 'balance' ORDER BY date ASC LIMIT 1
UNION
SELECT * FROM statement WHERE date >= a and date <= c and type != 'balance' ORDER BY date ASC

Upvotes: 2

Related Questions