CODEWITHSUNDEEP
mysql
Jude
Jude

Reputation: 159

How do I select multiple values in the same column?

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.

Upvotes: 14

Views: 92779

Answers (2)

Viral Jain
Viral Jain

Reputation: 1014

$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...

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166606

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.

Upvotes: 18

Related Questions