Retrieving multiple values from table using multiple WHERE conditions

Question

I have this table in MYSQL database (see image attached)

database

I would like to retrieve the column "cable" for three different apartments. This is what I have in my code so far, but it is not working, any idea why?

try
{
        //Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        //Connection connection=DriverManager.getConnection("jdbc:odbc:db1","","");
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        Connection connection=DriverManager.getConnection("jdbc:mysql://localhost/etoolsco_VecinetSM?user=etoolsco&password=g7Xm2heD41");
        Statement statement=connection.createStatement();
        String query;
        query="SELECT cable FROM flatene2013 WHERE Apto='5C' JOIN SELECT cable FROM flatene2013 WHERE Apto='5Cest1' JOIN SELECT cable FROM flatene2013 WHERE Apto='5Cest2'";            
        ResultSet resultSet=statement.executeQuery(query);
        while(resultSet.next())
    {%>
    <tr>

    <td>&nbsp;</td>
    <td align="right"><font size="-1"><%out.println(resultSet.getBigDecimal(1)+"");%></font></td>
    <td align="right"><font size="-1"><%out.println(resultSet.getBigDecimal(2)+"");%></font></td>
    <td align="right"><font size="-1"><%out.println(resultSet.getBigDecimal(3)+"");%></font></td>
    </tr>
    <%
    }

John Woo · Accepted Answer · 2013-01-22 00:09:04Z

2

use UNION instead of JOIN

SELECT  cable , Apto
FROM    flatene2013 
WHERE   Apto='5C' 
UNION
SELECT  cable , Apto 
FROM    flatene2013 
WHERE   Apto='5Cest1' 
UNION
SELECT  cable  , Apto
FROM    flatene2013 
WHERE   Apto='5Cest2'

or if you dont mind about the Apto

SELECT  cable , Apto
FROM    flatene2013 
WHERE   Apto IN ('5C' , '5Cest1', '5Cest2')

UPDATE

SELECT  MAX(CASE WHEN Apto = '5C' THEN  Cable ELSE NULL END) as `5c`,
        MAX(CASE WHEN Apto = '5Cest1' THEN  Cable ELSE NULL END) as `5Cest1`,
        MAX(CASE WHEN Apto = '5Cest2' THEN  Cable ELSE NULL END) as `5Cest2`
FROM
        (
            SELECT  cable , Apto
            FROM    flatene2013 
            WHERE   Apto IN ('5C' , '5Cest1', '5Cest2')
        ) s

edited Jan 22, 2013 at 0:09

answered Jan 21, 2013 at 15:36

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

4 Comments

Sheeyla Over a year ago

@JW great, it works, but I am having issues with placing the values in a <table> as in my example shown above. I need to have the three values placed in three different columns of my table

Sheeyla Over a year ago

@JW i get the following error: An error occurred at line: 474 in the jsp file: /2013/enero/avisocobro5C.jsp String literal is not properly closed by a double-quote 472: Statement statement=connection.createStatement(); 473: String query; 474: query="SELECT MAX(CASE WHEN Apto = '5C' THEN Cable ELSE NULL END) as 5c 475: MAX(CASE WHEN Apto = '5Cest1' THEN Cable ELSE NULL END) as 5Cest1 476: MAX(CASE WHEN Apto = '5Cest2' THEN Cable ELSE NULL END) as 5Cest2 477: FROM

Sheeyla Over a year ago

@JW any ideas? I am going crazy with this!

John Woo Over a year ago

@Sheeyla do you have a comma after each case?

sbeliv01 · Accepted Answer · 2013-01-21 15:37:38Z

1

Try using WHERE IN:

SELECT cable FROM flatene2013 WHERE Apto IN ('5C','5Cest1','5Cest2')

answered Jan 21, 2013 at 15:37

sbeliv01

11.8k2 gold badges26 silver badges25 bronze badges

3 Comments

Sheeyla Over a year ago

great, it works, but I am having issues with placing the values in a <table> as in my example shown above. I need to have the three values placed in three different columns of my table.

Sheeyla Over a year ago

It is only retrieving the value for Apto 5C. I am using resultSet.getBigDecimal(1) as it is only one value that I need for each Apto, right? ... I am only getting the value for 5C three times.

Sheeyla Over a year ago

and I get this error: java.sql.SQLException: After end of result set

Collectives™ on Stack Overflow

Retrieving multiple values from table using multiple WHERE conditions

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related