返回一个表中的所有行，并与另一个表中的行子集匹配?

I have the following two tables:

我有以下两张表:

rsrpID  rsrpName
1       Library Catalog
2       Interlibrary Loan
3       Academic Search Complete
4       JSTOR
5       Project Muse
6       LibGuides
7       Web Resource
8       Other (please add to Notes)
9       Credo Reference

rsriID  rsrirsrpID  rsrisesdID
603     6           243
604     1           243
605     7           243
606     8           244
607     6           245
608     8           245

What I'm trying to do is return the whole first table, and, for those rows in the second table that match the rsrpID in the first table, return those on the relevant rows alongside the first table, for example:

我要做的是返回整个第一个表，并且，对于第一个表中与rsrpID匹配的那些行，将它们返回到第一个表旁边的相关行中，例如:

rsrpID  rsrpName                    rsrisesdID
1       Library Catalog             243
2       Interlibrary Loan           
3       Academic Search Complete    
4       JSTOR                       
5       Project Muse                
6       LibGuides                   243
7       Web Resource                243
8       Other (please add to Notes) 
9       Credo Reference

...but I can't for the life of me figure out a join statement that'll return this. Currently the query I was given is

…但是我不能让我的生活找到一个连接语句来返回这个。当前给出的查询是

select rp.rsrpID as ID, rp.rsrpName as Name,
    (select if((count(rsrisesdID) > 0), 'checked', '') 
         from resourcesintroduced ri 
        where (ri.rsrirsrpID = rp.rsrpID) 
          and (rsrisesdID = 243) ) as 'checked' 
  from resourcesintroduced ri,
     resourcepool rp 
 where rsrisesdID = 243 
 group by ID 
 order by Name asc;

As you can see that query is clunky and, if a particular rsrisesdID doesn't appear at all, then the query returns no rows at all.

正如您所看到的那样，查询是笨拙的，并且，如果某个特定的rsrisesdID根本没有出现，那么该查询将不会返回任何行。

3 个解决方案

#1

You are looking for an Outer Join:

您正在寻找一个外部连接:

select rp.rsrpID as ID, rp.rsrpName as Name, ri.rsrisesdID
  from resourcepool rp 
  left outer join resourcesintroduced ri on (ri.rsrirsrpID = rp.rsrpID and ri.rsrisesdID = 243)

#2

You use a LEFT JOIN

使用左连接。

SELECT
    rsrpID,
    rsrpName,
    vrsrisesdID
FROM
    rp LEFT JOIN
    ri ON rp.rsrpID = ri.rsrirsrpID

Which returns:

1   Library Catalog                 243
2   Interlibrary Loan               NULL
3   Academic Search Complete        NULL
4   JSTOR                           NULL
5   Project Muse                    NULL
6   LibGuides                       245
6   LibGuides                       245
7   Web Resource                    243
8   Other (please add to Notes)     244
8   Other (please add to Notes)     245
9   Credo Reference                 NULL

Depending on the flavor of DBMS, you may have to use LEFT OUTER JOIN.

根据DBMS的风格，您可能不得不使用左外连接。

Hope this helps!

希望这可以帮助!

#3

You can get data by using LEFT OUTER JOIN as mentioned above but all the matching columns will be empty as the SQL engine will be confused from which table it should display the results for that column. So to come over this you have to explicitly mention identical column names. In your case it is not applicable as the column names are different. But for those who need help with this, you have to use something like below,

您可以使用上面提到的左外连接来获取数据，但是所有匹配的列都是空的，因为SQL引擎会混淆显示该列的结果的表。因此，为了克服这个问题，你必须明确地提到相同的列名。在您的例子中，它不适用，因为列名不同。但是对于那些需要帮助的人，你必须使用如下的方法，

*, Table1.ID, Table2.ID FROM ......

It will show you proper data.

它会显示正确的数据。

Hope this helps someone in need. ;)

希望这能帮助有需要的人。,)

#1