I have 9 fields and I need to see all the data from these fields which have a particular set of IDs. Could any one tell me the SQL query for it?
Ex: Database contains 100 records. I need to select a list of 20 IDs from the field BusID and it's corresponding rows.
SELECT *
FROM `Buses`
WHERE `BusID` I am stuck after this.. how do I put in the list of 20 BusIds here?
You want to add the IN() clause to your WHERE
SELECT *
FROM `Buses`
WHERE `BusID` IN (Id1, ID2, ID3,.... put your ids here)
If you have a list of Ids stored in a table you can also do this:
SELECT *
FROM `Buses`
WHERE `BusID` IN (SELECT Id FROM table)
I have 3 fields to fetch from Oracle Database,Which is for Forex and Currency Application.
SELECT BUY.RATE FROM FRBU.CURRENCY WHERE CURRENCY.MARKET =10 AND CURRENCY.CODE IN (‘USD’, ’AUD’, ‘SGD’)
You're looking for the IN() clause:
SELECT * FROM `Buses` WHERE `BusID` IN (1,2,3,5,7,9,11,44,88,etc...);
I strongly recommend using lowercase field|column names, it will make your life easier.
Let's assume you have a table called users with the following definition and records:
id|firstname|lastname|username |password
1 |joe |doe |[email protected] |1234
2 |jane |doe |[email protected] |12345
3 |johnny |doe |[email protected]|123456
let's say you want to get all records from table users, then you do:
SELECT * FROM users;
Now let's assume you want to select all records from table users, but you're interested only in the fields id, firstname and lastname, thus ignoring username and password:
SELECT id, firstname, lastname FROM users;
Now we get at the point where you want to retrieve records based on condition(s), what you need to do is to add the WHERE clause, let's say we want to select from users only those that have username = [email protected] and password = 1234, what you do is:
SELECT * FROM users
WHERE ( ( username = '[email protected]' ) AND ( password = '1234' ) );
But what if you need only the id of a record with username = [email protected] and password = 1234? then you do:
SELECT id FROM users
WHERE ( ( username = '[email protected]' ) AND ( password = '1234' ) );
Now to get to your question, as others before me answered you can use the IN clause:
SELECT * FROM users
WHERE ( id IN (1,2,..,n) );
or, if you wish to limit to a list of records between id 20 and id 40, then you can easily write:
SELECT * FROM users
WHERE ( ( id >= 20 ) AND ( id <= 40 ) );
I hope this gives a better understanding.
You can try this
SELECT * FROM Buses WHERE BusID in (1,2,3,4,...)
Try the following code:
SELECT *
FROM users
WHERE firstname IN ('joe','jane');
Source: Stackoverflow.com