在第一个tbl中获取所有记录，并在第二个tbl中获取匹配(指示已存在的记录) [英] Get all records in first tbl and matching in second tbl (indicating ones that existed)

问题描述

试图从UsersDataTbl获取所有UserName，并从EnrollmentsTbl匹配UserName(如果存在).但我想确定EnrollmentsTbl中存在哪些记录，而不要创建2条记录.也许添加一个布尔表达式，如果存在，则获取1；如果不存在，则获取0.

Looking to get ALL UserName from UsersDataTbl and matching UserName from EnrollmentsTbl if it exist. but I want to identify which ones exist in the EnrollmentsTbl and not create 2 records. Maybe add a boolean expression that gets 1 if exist and 0 if not.

我的SQL仅返回EnrollmentsTbl

SELECT u.UserName
     , e.Completed 
  FROM UsersDataTbl u
  LEFT 
  JOIN EnrollmentsTbl e
    ON u.UserName = e.UserName 
 WHERE e.ClassName LIKE 'Word%' 
   AND u.UserName LIKE 'bar%' 

附加问题:

(SELECT u.UserName, u.LastName, d.Station
  , (e.UserName IS NOT NULL) as completedl 
  FROM UsersDataTbl u
  LEFT 
  JOIN EnrollmentsTbl e
    ON u.UserName = e.UserName 
        AND e.ClassName LIKE 'Word%') 
  INNER JOIN UsersDataCareerTbl d 
  ON u.UserName = d.UserName
  WHERE u.Career = 1 AND Active = 1 ORDER BY u.LastName

推荐答案

您可以使用is not null来测试字段的值是否为null并返回true或false.如果EnrollmentsTbl中不存在用户名，则左联接将在其位置返回null.

You can us the is not null to test if a field's value is null or not and return a true or false. If the username does not exist in EnrollmentsTbl then the left join will return null in its place.

我将e.ClassName LIKE 'Word%条件从where子句移到了连接条件，因为它将left join更改为inner join，从而破坏了查询的全部目的.

I moved the e.ClassName LIKE 'Word% criterion from the where clause to the join condition, since it changed the left join into an inner join defeating the whole purpose of the query.

SELECT u.UserName
     , (e.UserName IS NOT NULL) as user_exists_in_EnrollmentsTbl 
  FROM UsersDataTbl u
  LEFT 
  JOIN EnrollmentsTbl e
    ON u.UserName = e.UserName 
        AND e.ClassName LIKE 'Word%'
 WHERE u.UserName LIKE 'bar%' 

这篇关于在第一个tbl中获取所有记录，并在第二个tbl中获取匹配(指示已存在的记录)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！