CODEWITHSUNDEEP
sqlitejoinsql-like
MiniMe
MiniMe

Reputation: 1275

Sqlite: select, join using like for the join fields

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

Upvotes: 2

Views: 4724

Answers (3)

Jamie Pollard
Jamie Pollard

Reputation: 1599

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;

Upvotes: 6

CL.
CL.

Reputation: 180161

This comparison is wrong:

v.TransitNumber like '%'+n.DeviceName+'%'

When you try it with some example values, you get:

'712' LIKE '%us712bs1%'

But you want it the other way around:

'us712bs1' LIKE '%712%'

i.e.:

n.DeviceName like '%'+v.TransitNumber+'%'

Upvotes: 0

Diéfani Favareto Piovezan
Diéfani Favareto Piovezan

Reputation: 239

First of all, DeviceName belongs to which table? Neighbors? If so, you need to use the foreign key that references Neighbors on VirtualControllers in order to make the JOIN and then use the LIKE in a WHERE clause. I will pretend you have a fk_id_neighbor on the VirtualControllers table and id_neighbor on Neighbor table. Something like this should work

SELECT n.DeviceName, n.Id_Neighbor, count(n.NeighborName) AS qtd, v.TransitNumber, v.FK_Id_Neighbor

FROM Neighbors n

LEFT JOIN VirtualControllers AS v ON v.TransitNumber.FK_Id_Neighbor= 
n.Id_Neighbor

WHERE v.TransitNumber LIKE '%'+n.DeviceName+'%'

GROUP BY n.DeviceName

You could use CONCAT in the LIKE statement but I do not recommend.

Upvotes: 1

Related Questions