Python SQL how to look for non-matches in other tables?

问题: I'm looking for a solution so that I can check for non-matches in other tables. Basically I have 3 tables (see below). Here I want to look into Table 1, and identify the...

问题:

I'm looking for a solution so that I can check for non-matches in other tables.

Basically I have 3 tables (see below). Here I want to look into Table 1, and identify the first row that doesn't have a match in either Name or Location. If both is recognized it should move to next row and check.

I have tried to accomplish this with SQL and looping through them, but as I only want the first row that doesn't match I haven't found a smooth solution (or pretty for that sake, as I'm fairly rookie-ish).

I'm pretty sure this can be accomplished with SQL.

Table 1

Id   Name  Location
1    John  Canada
2    James Brazil
3    Jim   Hungary

Table 2 - Recognized Names

Id   Name
1    John
2    James

Table 3 - Recognized Locations

Id   Location
1    Brazil
2    Hungary

So I want to select from Table 1, where Name can't find a match in Table 2 or where Location can't find a match in Table 3.

In my example from above the result should be Id = 1, as Location is not in Table 3.

Thanks in advance.


回答1:

You can use not exists to select if some sub-query doesn't select a row:

select
  *
from
  Table1 t1
where
  not exists (
    select * from Table2 t2 where t2.`Name` = t1.`Name`
  ) and
  not exists (
    select * from Table3 t3 where t3.`Location` = t1.`Location`
  )
order by
  t1.Id
limit 1

It's not a very complicated query, but still some things are going on, so here is the same one again, but with comments to explain the various parts:

select
  /* I select all columns, *, for the example, but in real life scenarios 
     it's always better to explicitly specify which columns you need. */
  *
from
  /* Optionally, you can specify a short or different alias for a table (t1)
     this can be helpful to make your query more readable by allowing you to explicitly
     specify where a column is coming from, without cluttering the query with long names. */
  Table1 t1
where
  /* exists takes a sub-query, which is executed for each row of the main query.
     The expression returns true if the subquery returns a row. 
     With not (not exists), the expression is inversed: true becomes false. */
  not exists (
    /* In MariaDB, backticks can be used to escape identifiers that also are 
       reserved words. You are allowed to use them for any identifier, but
      for reserved word identifiers, they are often necessary. */
    select * from Table2 t2 where t2.`Name` = t1.`Name`
  )
  /* Combine the two subqueries. We only want rows don't have a matching row 
     in sub-query one, and neither in sub-query two. */
  and
  not exists (
    select * from Table3 t3 where t3.`Location` = t1.`Location`
  )
/* Specify _some_ ordering by which you can distinguish 'first' from 'second'. */
order by
  t1.Id
/* Return only 1 row (the first according to the order clause) */
limit 1
  • 发表于 2019-01-05 22:44
  • 阅读 ( 159 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除