Get rows from primary table, and also a count of how many times that record appears in a secondary table (including 0's)

Question

I have a Dogs table, a Kennels table and Visits table that contains DogId and KennelId columns.

I am trying to get a full list of all the dogs, with a column showing the number of visits to a particular kennel, so many of the results will contain a 0 as the visit count.

This is what I've tried:

select dog.*, visits.visitCount FROM 
(select * from Dogs) as dog, 
(select COUNT (Visits.Id) as visitCount from Visits INNER JOIN Dogs ON Dogs.Id = 
Visits.DogId where KennelId = 'E15A8C60-E0FE-472D-9CC4-08DA251A992F') as visits

With this statement, I end up with all of the dogs, but with the same visit count for all, which is incorrect. I assume my count function is simply executed once with the result repeated for the remaining rows. I do not know how to correct this. Any help will be much appreciated!

Answer 1

It has a couple of bugs in its code.

1. builder visibility is not required

2. in the parameters of its functions it has a bad type 'stringmemory' the variable would be type 'sting' and it would be read from memory.

In this way your code would look like this:

pragma solidity ^0.8.6;
contract mycontract{

    address owner;
    string name;
    bool visible;
    uint16 count;

constructor () {
    owner=msg.sender;    
}

    function changname (string memory _name) public returns (string memory){
        if(msg.sender==owner){
            name=_name;
            return "sucsesss";
        }else{
            return "acsess denid";
        }
    }

    function showname () view public returns(string memory){
        return name;
    }
}