How to join SELECT with LIKE from many tables?

Question

I am trying to create a query that searches for all records that have a similar attribute:

select * from table_A where fullname like in (select firstname from employees where X) 

The only thing is that it’s just my pseudocode, I actually sort by many tables, so the real query that I have at the moment looks something like this:

 select * from devices where devicename like in (select X from X1 where T in (select T from T1 where Y in (select Y from Y1 where Z in (select Z from Z1 where AreaName = '74'; 

I am trying to use the join command, but am very confused about how to apply it to this situation.

Answer 1

Try:

 SELECT d.* FROM devices d INNER JOIN X1 ON d.devicename like '%'||x1.X||'%' INNER JOIN T1 ON x1.T = T1.T INNER JOIN Y1 ON T1.Y = Y1.Y INNER JOIN Z1 ON Y1.Z = Z1.Z WHERE Z1.AreaName = '74' 

Answer 2

Here you go ....

 SELECT D.* from Devices D inner join X1 X on D.devicename like XX inner join T1 T on TT = XT inner join Y1 Y on YY = TY inner join Z1 Z on YZ = Z1.Z AND Z.Areaname = '74'; 

Answer 3

Ok, I give you a second request with Join syntax (well, I'm trying):

 select * from Z1 Z1_1 JOIN Y1 Y_1 ON Y_1.Z=Z1_1.Z JOIN T1 ON T1.Y=Y_1.Y JOIN X1 X1_2 ON X1_2.T=T1.T JOIN devices d ON d.devicename=X1_2.X where Z1_1.AreaName = '74'; 

Answer 4

Try the following:

 SELECT d.* FROM devices d INNER JOIN X1 ON d.`like` = x1.X INNER JOIN T1 ON x1.T = T1.T INNER JOIN Y1 ON T1.Y = Y1.Y INNER JOIN Z1 ON Y1.Z = Z1.Z WHERE Z1.AreaName = '74' 

Answer 5

The join command crosses the two tables and then selects only tuples / rows where the common column names have the same value.

So for your request:

 select * from table_A where fullname like in (select firstname from employees where X) 

:

 select * from table_A left join employees on table_A.fullname = employees.firstname where X