CODEWITHSUNDEEP
mysqlsqldatabasedatabase-designdatabase-schema
Kishan Bheemajiyani
Kishan Bheemajiyani

Reputation: 3439

Fetching data from database and Filter Results

I'm getting the first 50 records using this query. However, there is a flag named read in the same table, and I only want to return the first 50 records that are not read (i.e. isread=false). How can I achieve this? Can anybody give me ideas for the required query? I have tried using sub queries.

SELECT * FROM notification WHERE toUserId = 'email' ORDER BY id DESC LIMIT 50;

Upvotes: 3

Views: 189

Answers (3)

Atul O Holic
Atul O Holic

Reputation: 6792

Try adding an AND condition to your WHERE clause: userID = 'email' AND flag = true. This will only return users with true value for flag of which you can get the top 50 by your limit condition.

Upvotes: 4

Anoop
Anoop

Reputation: 369

Since you want to get the first 50 values regardless of the IsRead condition and then filter it, you can try this but I populated the query in Sql Server.

SELECT * FROM (SELECT TOP 50 * FROM notification) S WHERE toUserId = 'email' and isread=false

This should help. You can try the same technique in MySql.

Upvotes: 1

Robert Seddon-Smith
Robert Seddon-Smith

Reputation: 1002

SELECT * FROM notification WHERE toUserId = 'email' AND isread = 0 ORDER BY id DESC LIMIT 50;

should work, though it does depend on you using 0 for false and 1 (or anything else) for true. If you have chosen the tinyint or BOOL type then this will usually work (note tinyint can be -9 to +9 so make sure you set 0 or 1 as your flags.

In my opinion, it is probably better to use the terms 1 and 0 rather than TRUE or FALSE (though this is a matter of aesthetics really) because the code is then more readily transferrable. It also prevents lazy coding.

Upvotes: 0

Related Questions