MySQL- How to fetch multiple values of same column using a single SQL query?

Question

I need to store each values from the Resultset to float variables. Currently I am using multiple SQL queries for this. I have combined the queries

float Wb_SWLat = 0;
float Wb_NELat = 0;
float Wb_SWLon = 0;
float Wb_NELon = 0;

float Nb_SWLat = 0;
float Nb_NELat = 0;
float Nb_SWLon = 0;
float Nb_NELon = 0;

//Query:
String qryWb = "select zone, SW_lat, SW_lon, NE_lat, NE_lon from tbl_zones";

try {
    Statement stmt = (Statement) conn.createStatement();

    Wb=stmt.executeQuery(qryWb);
    while(Wb.next()){ 
        if (Wb.getString("zone") == "west"){
            Wb_SWLat=Wb.getFloat("SW_lat");
            Wb_NELat=Wb.getFloat("NE_lat");
            Wb_SWLon=Wb.getFloat("SW_lon");         
            Wb_NELon=Wb.getFloat("NE_lon");
        }
        else if (Wb.getString("zone") == "north"){
            Nb_SWLat=Wb.getFloat("SW_lat");
            Nb_NELat=Wb.getFloat("NE_lat");
            Nb_SWLon=Wb.getFloat("SW_lon");         
            Nb_NELon=Wb.getFloat("NE_lon");
        }
    }
} catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

Is this is the efficient way? or right way of doing this?

Below is My MySQL table, tbl_zones:-

pk_id  jn    zone     SW_lat     SW_lon     NE_lat     NE_lon
--------------------------------------------------------------
 1     j1    west     9.99404    76.3567    9.99419    76.3572
 2     j2    north    9.99419    76.3572    9.99471    76.3573
 3     j3    south    9.99384    76.3572    9.99413    76.3573
 4     j4    east     9.99413    76.3574    9.99426    76.3577

Dear programmers, Any piece of code is highly appreciated. Thanks in advance. — jasim
– jasim, Commented Dec 18, 2016 at 6:30

MordechayS · Accepted Answer · 2016-12-18 07:01:56Z

1

Use the OR operator in the where clause. That will get you both lines needed in one query (and roundtrip time to DB).

Now, in order to identify each fetched line, add zone to your select statement, and use an if clause in your code to identify the current line in the ResultSet object.

The query should look something like this:

select SW_lat, SW_lon, NE_lat, NE_lon, zone from tbl_zones where zone='west' OR zone='north

answered Dec 18, 2016 at 7:01

MordechayS

1,5462 gold badges20 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

20 Comments

MordechayS Over a year ago

@jasim you can iterate through the fetched lines in the ResultSet, as shown here.

MordechayS Over a year ago

Not through variables in the ResultSet, but through lines. That will help you use the two fetched lines. Understand?

MordechayS Over a year ago

Select "zone" too, and you it as a identifier of each line, as you can't depend on order.

MordechayS Over a year ago

Efficient? Yes. Pretty? Not so much. But for the scope of this question - it's enough. If you found my answer as helpful, please mark it ad accepted.

MordechayS Over a year ago

@jasim pretty = remove code duplication + encapsulate the float extraction in a method that gets a line if results and returns a composite data-structure...

|

Collectives™ on Stack Overflow

MySQL- How to fetch multiple values of same column using a single SQL query?

1 Answer 1

20 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

20 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related