Sqlite: select, join using like for the join fields

Question

This is in sqlite 3
I have location numbers and addresses in one table (stored as CHAR)
I have device names and other details in another table, where device names are stored as CHAR and the location number is a substring or equal with the device name

I would like to be able to select all the locations and calculate the number of devices that are at that location based,grouped by the location number

Here is what I am using

SELECT v.[TransitNumber],DeviceName,count(NeighborName) from Neighbors n
left join VirtualControllers v on v.TransitNumber like '%'+n.DeviceName+'%'
group by DeviceName

This returns me Null for the value of the transit number and I don't undertand why

Here is some data

TransitNumber   DeviceName  count(NeighborName)  
                712bs1       2  
                712bs2       2  

the transit number should have been 712 In this case (BTW I am actually counting how many PC are connected to each device but I would like to get how many PCs are in that location
The result should be

TransitNumber   count(NeighborName)
712               4

Here is some data example and what I want to achieve

    Neighbours table

    DeviceName  Interface   NeighbourName
    us712bs1        fa0/1       PC1
    us712bs1        fa0/2       PC2
    us712bs2        fa0/1       PC3
    us712bs2        fa0/2       PC4


   VirtualCotroller table

    TransitNumber   Address
    712             New York, TimeSquare


    Expected result 

    TransitNumber   PCs
    712             4

Answer 1

Here is a fiddle with what you want.

I've left it as a left outer join as this will mean you will get counts of zero included for VirtualControllers that have no Neighbours (rather than just exclude the missing rows - if you want to exclude them change it to be an INNER JOIN)

The main issue was your concatenation in your LIKE, SQLite uses double pipe || for string concatenation not +.

There are two selects in the fiddle above, the first gives what you asked for, with the count of 4. The second also groups by the device name, in case you wanted to see the distinction between the two.

Your SQL ends up being as follows:

SELECT TransitNumber, DeviceName, COUNT (NeighbourName)
FROM VirtualController V
LEFT OUTER JOIN Neighbours N ON N.DeviceName LIKE '%' || V.TransitNumber || '%'
GROUP BY TransitNumber;