1

I've an sql table like this:

SID|SERVICE_SID|ATTRIBUTE_SID|VALUE
1    2           5             "VAL1"
2    2           4             "VAL8"
3    2           3             "VAL4"
4    11          5             "VAL1"
5    11          3             "VAL4"

I'd like to group by service_SID where for the same service_sid:

AND (attribute_sid = 5 && Value="VAL1")

AND (attribute_sid = 4 && Value="VAL8")

So, in this case, only a row with service_sid = 2 should be returned.

Is this a query like this even possible? What would it look like?

  • Which DBMS do you use? – ypercubeᵀᴹ Apr 24 '15 at 9:27
  • HSQL for the moment, but i'm planning to use MS Sequel server. But ideally, I'd like it to be as much dbms agnostic as possible.. – Mark Apr 24 '15 at 9:28
  • What is the final goal, because if you want to group by service_SID why just you use GROUP BY service_SID? – Bogdan Bogdanov Apr 24 '15 at 9:31
3

You can use INTERSECT which is available in both HSQLDB and SQL Server

SELECT SERVICE_SID
FROM SQLTable
WHERE attribute_sid = 5 AND Value='VAL1'
INTERSECT
SELECT SERVICE_SID
FROM SQLTable
WHERE attribute_sid = 4 AND Value='VAL8';

You can then join the result of this query with your table and group by SERVICE_SID

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.