JOIN and GROUP BY

[gordonisnz]

Hello. I'm wondering if anyone can assist me with a JOIN and a GROUP BY process.

I've done joins in the past, but not sure how to do a GROUP BY in this instance.

Here is the basic of the databases

MAIN DATA
:- Business Id 1097-05-09 (the ID is not a date - just a 3 number sequence)
:- business name (obvious)
:- business address (obvious)

ANIMAL DATABASE
:- Business ID (cross referenced to MAIN DATA)
:- Unique ID
:- TYPE (cat, dog etc.)
:- name of pet/animal.

What I'm planning to do, is have 3 x "reserved" animal ID's per Entity - of "RESERVED".

example (animal database):-

Bus ID, Unique ID, Type, Name

1097-05-05, 242562, CAT, George
1097-05-05, 242566, DOG, Bounce
1097-05-05, 242574, CAT, Smittens
1097-05-05, 242574, UNKNOWN, RESERVED
1097-05-05, 242577, UNKNOWN, RESERVED
1097-05-05, 242578, UNKNOWN, RESERVED

1097-05-09, 242678, CAT, Smoot
1097-05-09, 242698, CAT, RESERVED

You'll see that 1097-05-05 already has 3 "RESERVED" names, I do not need any more.

Now 1097-05-09 has only 1 "RESERVED" name.

Is there a way to group by the Bus ID, matching the name of "RESERVED" and if there are fewer than 3, report back with the Bus ID. I'll then use that ID to schedule the creation of one more entry..
in this example, i'll want "1097-05-09"

Will GROUP BY be the best way to do this? (also find new entities with 0 animals )

 

[MilesWeb]

Yes, you can accomplish this by combining JOIN and GROUP BY in SQL.

SELECT m.`Business ID`FROM MAIN_DATA mLEFT JOIN (    SELECT `Business ID`, COUNT(*) AS reserved_count   
FROM ANIMAL_DATABASE   
 WHERE Type = 'RESERVED'    GROUP BY `Business ID`) a ON m.`Business ID` = a.`Business ID`WHERE COALESCE(a.reserved_count, 0) < 3

i hope it will work for you.