SELECT from a distinct list

I'm running the folowing query:

SELECT DISTINCT hardware_id, model, make from Table1 order by hardware_id;

My problem is that in the set of result, I want to see the results where only if hardware_id appears more than once.

Example:

hardware 1 model1 make 1

hardware 1 model2 make 1

hardware 2 model2 make 1 > I don't want to see this because there's only 1 occurence of hardware 2

hardware 3 model2 make 1

hardware 3 model2 make 1

Any idea how I can do this?

=====

The prob is that I can't use the following query because of my DISTINCT Flag:

SELECT hardware_id,model,make from Table1 WHERE hardware_id IN (SELECT DISTINCT hardware_id, model, make from Table1 order by hardware_id) where count(hardware_id) >= 2;

I need absolutely to use distinct (hardware_i开发者_开发百科d, model, make)

Any idea?

SELECT hardware_id, model, make 
FROM Table1 AS t
WHERE EXISTS
      ( SELECT * 
        FROM Table1 AS td
        WHERE td.hardware_id = t.hardware_id
          AND td.id <> t.id                 --- if id is the PK of the table
      )
ORDER BY hardware_id

Since the table has duplicate (hardware_id, model, make) triplets, you can use this instead:

SELECT DISTINCT hardware_id, model, make 
FROM Table1 AS t
WHERE EXISTS
      ( SELECT * 
        FROM Table1 AS td
        WHERE td.hardware_id = t.hardware_id
          AND (td.model, td.make) 
              <> (t.model, t.make)               
      )
ORDER BY hardware_id

SELECT hardware_id, model, make 
FROM table1
GROUP BY hardware_id  --  group by orders the rows ASC as a side effect.
HAVING count(hardware_id) > 1

However this will select * random * values for model and make.
You might want to consider using:

SELECT hardware_id, GROUP_CONCAT(DISTINCT CONCAT(model,' - ',make)) AS modelmakes 
FROM table1
GROUP BY hardware_id  --  group by orders the rows ASC as a side effect.
HAVING count(hardware_id) > 1

You can also do a join against the first select to get all rows you need, you should not need distinct.

SELECT t1.hardware_id, t1.make, t1.make 
FROM table1 t1
INNER JOIN (
    SELECT hardware_id 
    FROM table1
    GROUP BY hardware_id  
    HAVING count(hardware_id) > 1
           ) t2 ON (t1.hardware_id = t2.hardware_id)

You will need to sub-query the hardware ids which are repeated, and join this list to your query. The sub-query could be:

SELECT hardware_id
FROM Table1
GROUP BY hardware_id
HAVING COUNT(hardware_id) > 1

Then, you can join it to your original query:

SELECT hardware_id, model, make 
FROM Table1
INNER JOIN (
    SELECT t1.hardware_id
    FROM Table1 t1
    GROUP BY t1.hardware_id
    HAVING COUNT(t1.hardware_id) > 1
) AS join1 ON (join1.hardware_id = Table1.hardware_id)
ORDER BY hardware_id ASC;

Try this query:

SELECT hardware_id, model, make from Table1 where hardware_id in 
    (select hardware_id 
     from TABLE1 group by hardware_id having count(hardware_id) > 1);

Another version:

SELECT hardware_id, model, make from Table1 t where EXISTS( 
    select hardware_id 
     from TABLE1 where hardware_id = t.hardware_id 
     group by hardware_id having count(hardware_id) > 1
);