-2

i have this table

id   owner   group     model  
1    1       shapes    circle  
2    1       shapes    rectangle  
3    1       shapes    squire  
4    2       fruits    apple  
5    2       fruits    orange  
6    2       fruits    banana  
7    3       shapes    circle  
8    3       shapes    rectangle  
9    3       shapes    star

which each owner select a group and enter 3 choice, now i want to find the owner who select shapes and entered circle and star.

it must return owner id = 3

John Woo
  • 258,903
  • 69
  • 498
  • 492
Mohammad Adibi
  • 19
  • 11

2 Answers2

1

Assuming that group and model are unique for every owner

SELECT  a.owner
FROM    tableName a
WHERE   a.[group] = 'shapes' AND
        a.model IN ('circle','star')
GROUP   BY a.owner
HAVING  COUNT(*) = 2

otherwise, you need to uniquely count the number of model

SELECT  a.owner
FROM    tableName a
WHERE   a.[group] = 'shapes' AND
        a.model IN ('circle','star')
GROUP   BY a.owner
HAVING  COUNT(DISTINCT a.model) = 2
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • thank you for your answer but i think it is mysql, i work with ms sql 2008. can you convert it? – Mohammad Adibi Sep 23 '13 at 19:01
  • here http://www.sqlfiddle.com/#!3/ce2f6/2 but you need to escape the column name `group` with bracket because it is a reserved keyword. – John Woo Sep 23 '13 at 19:08
  • Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'group'. – Mohammad Adibi Sep 23 '13 at 19:18
  • thank you new sqlfiddlle is working,thank you let me check and understand yore solution, let ask if i have any problem, thanks again. – Mohammad Adibi Sep 23 '13 at 19:23
  • is there any tutorial to describe your method? i search 1 week in web, but i didn't this use of group by, that's great. – Mohammad Adibi Sep 23 '13 at 19:30
  • you problem is somewhat called `Relational Division` see here [SQL of Relational Division](https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/) – John Woo Sep 23 '13 at 19:41
0

Within the specified parameters this will work: SELECT owner WHERE [group] = 'shapes' AND model = 'circle' INTERSECT SELECT owner WHERE [group] = 'shapes' AND model = 'star'

nimdil
  • 1,361
  • 10
  • 20
  • how to refer to my table(for example: options) it sqys: – Mohammad Adibi Sep 23 '13 at 19:35
  • Msg 207, Level 16, State 1, Line 1 Invalid column name 'group'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'model'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'owner'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'group'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'model'. Msg 207, Level 16, State 1, Line 1 Invalid column name 'owner'. – Mohammad Adibi Sep 23 '13 at 19:35
  • You need to add FROM [table name] before the WHERE clause. I don't know how you called your table. – nimdil Sep 24 '13 at 10:26