1

I have table structure in this format:

# id  attributeId   projectId
'28'    '20'          '87' 
'29'    '21'          '87' 
'30'    '22'          '570' 
'31'    '20'          '570' 
'32'    '30'          '570'

I want to pass attribute IDs as parameter and get the projectId which has all attributes assigned to it.

For ex : if I pass 20,30 attribute Ids, i should only get 570 project ID and not 87, as 30 is not assigned to 87.

SELECT * FROM fm05__project_attributes where attributeId in (20,30);

The above query gives me, the below result which is not correct.

id  attributeId   projectId
28    20              87    
31    20              570   
32    30              570
James Z
  • 12,209
  • 10
  • 24
  • 44
Danish
  • 43
  • 5

1 Answers1

1

Try this:

SELECT projectId
FROM  fm05__project_attributes
WHERE attributeId IN (20,30)
GROUP BY projectId
HAVING COUNT(*) = 2
gotqn
  • 42,737
  • 46
  • 157
  • 243
  • (Or, possibly, (DISTINCT attributeid) ) – Strawberry Oct 31 '20 at 08:43
  • There could be many attribute IDs passed as parameters and we do not know the count of either the number of attribute Ids or the project Ids – Danish Oct 31 '20 at 08:45
  • @danish it would almost inconceivable that this information would be 'unknown' – Strawberry Oct 31 '20 at 08:52
  • attributeIds could be 20,21,24,30, in this case the user would select 4 attribute Ids, we want to fetch all the project Ids to which all these attributes are assigned. – Danish Oct 31 '20 at 08:55
  • @Danish What's the issue? You can easily count how many items you are passing and put a variable in the `HAVING COUNT(*)` clause. – gotqn Oct 31 '20 at 08:58