enter image description here

Used by PostgreSql.

It is necessary to make a SELECT , which will output a row from table 1, which is not in the second table (t1.sip = t2.sip, t2.name содержит t1.mac) .

That is, the result should be:

user1 | bbbbbbbbbbbb

I tried to do this:

 SELECT t1.sip, t1.mac FROM t1 LEFT JOIN regs ON t1.sip=t2.sip where t2.sip IS NULL;

Did not user1 as user1 is in both tables, additional was necessary. check if t2.name contains t1.mac .

So I tried the following:
SELECT DISTINCT( t1.sip), t1.mac FROM t1 LEFT JOIN t2 ON t1.sip=t2.sip t2.ua NOT LIKE CONCAT('%',wa.mac,'%');

The result is:

user1 | bbbbbbbbbbbb

But if in t2 all the same record appears

user1 | test (bbbbbbbbbbbb)

, then the result will be:

user1 | bbbbbbbbbbbb

user1 | aaaaaaaaaaaa

But it is necessary for me that, as a result, there should not be these lines, since they fit the conditions

 t1.sip = t2.sip, t2.name содержит t1.mac
  • left join + where is null - Akina
  • left join + where is null Not suitable. At least I did not succeed. The difficulty is that user1 and bbbbbbbbbbbb seem to be in table 2, but there is no link. I do not know how to write what I need. - Sergey Rakov
  • @Akina not exists better - nörbörnën
  • @SergeyRakov in the left join in on you can write all the required conditions
  • SELECT t1. * FROM TABLE1 t1 LEFT OUTER JOIN TABLE2 t2 ON t2.SIP = t1.SIP WHERE t1.MAC ~ * substring (t2.MAC from 5 for 14); - Yaroslav

1 answer 1

Here is the request I needed:

 SELECT DISTINCT( t1.sip), t1.mac FROM t1 LEFT JOIN t2 ON t1.sip = t2.sip and t2.name LIKE CONCAT ('%',t1.mac,'%') WHERE t2 IS NULL;
  • and it's normal that your TABLE1.SIP is not unique - Yaroslav