在MySQL查询中相互检查两个表

I feel like I'm writing a word problem, but it's really puzzling me and I really hope someone here can solve it:

我觉得我正在写一个单词问题,但这真让我感到困惑,我真的希望有人可以解决它:

I want to select one row from table A. Table A includes the attributes Name and Number. But before I finish the query, I want to check it against table B. Table B includes Name, Number, and the Username of the user. Based on the users' input, it inserts rows into table B that include their Username along with a Name and Number. Now in my query where I select a row from table A, I want to make sure that there are no rows in table B with matching Name and Number for that particular User.

我想从表A中选择一行。表A包括属性Name和Number。但在我完成查询之前,我想针对表B进行检查。表B包括用户的名称,编号和用户名。根据用户的输入,它将行插入表B,其中包括用户名以及名称和编号。现在在我从表A中选择一行的查询中,我想确保表B中没有与该特定用户匹配的名称和编号的行。

I have tried WHERE (A.Name = B.Name AND A.Number = B.Number AND B.Username != '$username') but I think I was way off base with that. Any help would be... amazing.

我已经尝试过WHERE(A.Name = B.Name AND A.Number = B.Number AND B.Username!='$ username')但是我觉得我已经离开那里了。任何帮助都会......太棒了。

3 个解决方案

#1

select a.id
from a
where
a.name=:name
and
not exists(select 1 from b where b.id=a.id and b.name=a.name)

#2

SELECT
   A.id
FROM
   A
   LEFT OUTER JOIN B ON
      (A.Name = B.Name AND A.Number = B.Number)
WHERE
   B.Name IS NULL
   AND B.Number IS NULL
   AND B.Username = ?

#3

  IF NOT EXISTS  ( SELECT 1 
       FROM tableA a
       INNER JOIN tableB b
       ON a.name = b.name
       AND a.number= b.number
       AND b.UserName = 'user' and b.name = 'name'and b.number = 'number')
  SELECT *
  FROM tableA x
  WHERE x.name = 'name'and x.number = 'number'