Writing a SELECT query for the following table - 【StackMirror】|mysql|sql|ms-access

Possible Duplicate:
Writing an SQL query to SELECT item from the following table

I am having trouble figuring out how to write a SELECT query that selects every cell from the table that has more than one of the same cell value in the column. For example if column name is "Cars" then select "Ford" only if "Ford" takes up more than one cell in the column.

2012-04-05 01:37
by CSoverIT

Can you post the table structure and the columns which are to be taken together - Chetter Hummin 2012-04-05 01:39

http://i44.tinypic.com/1zdcc9j.png .... heres a pic of the table, I need to select all the cells in the 'snum' column that appear more than once... (the only number that appears more than once in the snum column is 3) - CSoverIT 2012-04-05 01:40

Here's a version w/o the extra column:

Select cars from (select cars, count(*) as nbr from theTable group by cars) 
where nbr > 1;

2012-04-05 01:44
by Scott Hunter

This works quite well, thank you! however, how would I modify this to include some additional information such as 'car buyer' from a different table that is related to this table... so I can display all the buyers that own two of the same type of car (two fords, two audi's, etc... - CSoverIT 2012-04-05 01:51

You may need to use the Having clause.

 SELECT count(*), cars
 FROM table
 GROUP BY cars
 HAVING count(*) > 1

2012-04-05 01:42
by Vincent Ramdhanie

thank you, though, that does not work, look at the pic link in the comment... - CSoverIT 2012-04-05 01:45

@CSoverIT Could you please clarify on the error/incorrect result - Chetter Hummin 2012-04-05 01:50

The syntax seems to by incorrect when I try to use this logic on Access SQL. The other answer works, however - CSoverIT 2012-04-05 02:02

Actually it works fine in Access. You may want to verify you have the correct table/column names in your version - Leigh 2012-04-05 05:55