The database I'm working on has tables services and orders.
Each service has a foreign key reference as a column fk_order.
Now, services have a type column. Whenever a new order is created in the web-app, they'll automatically get a service the type of which is 'ORDERHANDLE'
There was a bug at some point that MIGHT have created a situation where some orders would've ended up with more than one service with the type 'ORDERHANDLE' referring to them.
What I'd like to do now is to make a query, that checks if there are multiple (more than one) service rows with the type 'ORDERHANDLE' referring to one fk_order. Because only one ORDERHANDLE type of service should be referring to one order.
Thank you for any advice.
Here is my query so far (yes I'm a noob.)
SELECT * FROM services
INNER JOIN orders
ON services.fk_order = order.id
WHERE services.type = 'ORDERHANDLE'
I just have no idea how to proceed from there.
You need to perform an aggregation of the services table. You will need to use the COUNT function to count the number of ORDERHANDLE occurences. I'm not proficient with sql-server but something like this should work:
SELECT count(services.id) FROM orders
INNER JOIN services
ON services.fk_order = order.id
WHERE services.type = 'ORDERHANDLE'
GROUP BY orders.id
HAVING count(services.id) > 1
