CODEWITHSUNDEEP
javapostgresqljdbc
Prince of Sweden
Prince of Sweden

Reputation: 475

SQL: Select multiple rows with the same ID

I have a bit of a problem. I have a table with names, IDs and different geometry like so:

ID  |  Name| RealID| Geometry| 
==========================================
1   |      Hampshire     |   3   |  0762453...
2   |      Hampshire     |   3   |  0156245...
3   |      Salt Lake     |   2   |  312455...
4   |      Hampshire     |   3   |  016422....

I have a need of selecting all Hampshire rows based on a list of IDs. As you can see my table has different geometries for say Hampshire. I need all of them somehow by just comparing against one ID I get from a list of them.

I get the list of IDs from Java. It is simply a list with one ID(3) so far.

Doing this: 

Select * from table where RealID = any(:listOfIds)

It only returns me one row no matter what if I send in Hampshire's ID in the list (3). I have tried something like this:

Select * from table where RealID IN (any(:listofids))

but the syntax is wrong and I'm not sure what to do to achieve my goal.

Upvotes: 0

Views: 1612

Answers (3)

Shubham Srivastava
Shubham Srivastava

Reputation: 1293

You can try the following syntax

SELECT * FROM table WHERE RealID in (listOfIDs)

for example:

SELECT * FROM table WHERE RealID in (3,2,6....)

You can try this also:

SELECT * FROM table WHERE FIND_IN_SET('3', RealID);

Upvotes: 0

Vipin Pandey
Vipin Pandey

Reputation: 907

can you try this one .

 Select * from problem where RealID IN (?,?,?);

? =here put on RealID according your requirement ex: 

SELECT * FROM table WHERE RealID in (3);

Upvotes: 0

Youcef LAIDANI
Youcef LAIDANI

Reputation: 60016

If you want to make some thing secure and clean code you can use this way :

String query = "Select * from table where RealID IN (";

for (int i = 0; i < list.lenght(); i++) {
    query += (i == 0 ? "" : ", ") + "?";
}
query += ")";
PreparedStatement preparedStatement = dbConnection.prepareStatement(query);
for (int i = 0; i < list.lenght(); i++) {
    preparedStatement.setInt(i, list.get(i));
}
ResultSet rs = preparedStatement.executeQuery(query);
while (rs.next()) {
    //get your informations here
}

You should to loop throw your list and use PreparedStatement so your query should look like this :

Select * from table where RealID IN(?, ?, ?, ?, ?)

This is safe way.

Upvotes: 1

Related Questions