Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I select multiple values in the same column?

Tags:

mysql

I am trying to select multiple values in a single column. Basically I want the query to select all those under column family with values Software_1Y,XI_1Y and P1_1Y

I am running this query :

SELECT `salesorder`
    ,`masterproduct`
    ,`family`
    ,`birthstamp`
    ,`duedate`
    ,COUNT(*) AS `total`
FROM `report`
WHERE `birthstamp` BETWEEN '$startDT'
        AND '$endDT'
    AND `family` = 'Software_1Y'
    AND `family = 'XI_1Y'
    AND `family` = 'PI_1Y'
GROUP BY `salesorder`
    ,`masterproduct`
    ,`family`
    ,`duedate`;

My query returns no rows but is I search each family one by one, I have values.

What is wrong with my query?

Also, my purpose is to get all the rows whose family values are Software_1Y, XI_1Y and PI_1Y.

like image 678
Jude Avatar asked Sep 26 '12 03:09

Jude


People also ask

How do you select a column with multiple values?

Note – Use of IN for matching multiple values i.e. TOYOTA and HONDA in the same column i.e. COMPANY. Syntax: SELECT * FROM TABLE_NAME WHERE COLUMN_NAME IN (MATCHING_VALUE1,MATCHING_VALUE2);

How do I select multiple values in one column in MySQL?

Learn MySQL from scratch for Data Science and Analytics To select multiple values, you can use where clause with OR and IN operator.

How can I get multiple values from a single column in SQL Server?

You can either loop through the rows with a cursor and append to a field in a temp table, or you could use the COALESCE function to concatenate the fields.


2 Answers

How about using IN instead

SELECT  `salesorder`,
        `masterproduct`,
        `family`,
        `birthstamp`,
        `duedate`, 
        COUNT( * ) AS `total` 
FROM    `report` 
WHERE   `birthstamp` BETWEEN '$startDT' AND '$endDT' 
AND     `family` IN ('Software_1Y','XI_1Y','PI_1Y')
GROUP BY    `salesorder`,
            `masterproduct`,
            `family`,
            `duedate`;

The reason why no values are returned, is because of this section

AND `family` = 'Software_1Y' 
AND `family = 'XI_1Y' 
AND `family` = 'PI_1Y'

family cannot be all 3 values at once, but it might be 1 of the 3 values.

That is why you would use IN.

Another way to look at it would be to use OR, but that gets really long winded.

like image 140
Adriaan Stander Avatar answered Oct 25 '22 23:10

Adriaan Stander


$query="   SELECT `salesorder`,`masterproduct`,`family`,`birthstamp`,`duedate`, COUNT( * ) AS `total` FROM `report` 
    WHERE `birthstamp` BETWEEN '$startDT' AND '$endDT' 
           AND (`family` = 'Software_1Y' 
           OR `family` = 'XI_1Y' 
           OR `family` = 'PI_1Y') 
    GROUP BY `salesorder`,`masterproduct`,`family`,`duedate` ";

It must be due to AND instead of OR while querying FAMILY column.

$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) 
{
//Operation you want to perform
}

@jude: It seems you are directly passing the query which is of type string directly as a parameter to mysql_fetch_array, which is wrong. Instead follow the above approach...

like image 27
Viral Jain Avatar answered Oct 25 '22 23:10

Viral Jain