问题描述:

suppose my table contains only 2 fields , ID and product and say my structure looks like this

1       microsoft

0      cisco

2       cisco

3      vmware

0      adobe

0      microsoft

I need to write a query that lists records having id=0 but only,

0      adobe

because the other two products miocrsoft and cisco have one other record that does have an ID and so I can exclude them.

I hope I am clear about what Im trying to achieve. Please let me know if this can be accomplished in sql

网友答案:

Yes this is possible using basic aggregation. Notice how I posted consumable data? This is something you should do. It took me far longer to create usable data than it did to write the query.

with something(Col1, Col2) as
(
    select 1, 'microsoft' union all
    select 0, 'cisco' union all
    select 2, 'cisco' union all
    select 3, 'vmware' union all
    select 0, 'adobe' union all
    select 0, 'microsoft'
)

select Col2
from something
group by Col2
having MAX(Col1) = 0
网友答案:

You can use NOT EXISTS:

SELECT id, product
FROM mytable AS t1
WHERE id = 0 AND NOT EXISTS (SELECT 1
                             FROM mytable AS t2
                             WHERE t1.product = t2.product AND t2.id <> 0)

Demo here

网友答案:

Try something like this

SELECT *
FROM table
WHERE id = 0 and product not in
(
    SELECT product
    FROM table
    WHERE id <> 0
)
网友答案:

you can use the following code to get the answer: In the first part of the code to get all products. The second part the same products set an array. In the end, the same products removed from the products of their ID 0

("my database name = stackoverflow" , "my table name = stack" , "my fields = id,name")

<?php 
$conn = mysql_connect('localhost','root','') or die ("erorr");
mysql_select_db('stackoverflow');
$query1 = "SELECT name FROM stack";
$result = mysql_query($query1);
echo "<b>Show the products:</b>";
echo "<br>";
$y;
$z;
$i = 0;
$k = 0;
while ($x = mysql_fetch_array($result)) {
    echo $x[0];
    echo "<br>";
    $y[$i] = $x[0];
    if ($i > 0) {
        for ($j=0; $j < $i; $j++) { 
            if ($y[$i] == $y[$j]) {
                if ($k > 0) {
                    for ($q=0; $q < $k; $q++) { 
                        if ($z[$q] == $y[$i]) {
                        break 2;
                        }
                    }
                }
                $z[$k] = $y[$i];
                $k++;
                break 1;
            }
        }
    }
    $i++;
}
echo "<hr>";
echo "<b>Show duplicate products:</b>";
echo "<br>";
$k=0;
while (isset($z[$k])) {
    echo $z[$k];
    $k++;
echo "<br>";
}
$query2 = "SELECT name FROM stack WHERE id=0";
$result = mysql_query($query2);
echo "<hr>";
echo "<b>Show answers:</b>";
echo "<br>";
while ($x = mysql_fetch_array($result)) {
    if (!in_array($x[0], $z)) {
    echo $x[0];
    echo "<br>";
    }
}
 ?>

But this kind of response, is not optimized and table bad design. Because every table should have a primary key and search on table by key.

相关阅读:
Top