问题:
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