如何进行mysql连接，其中连接可能来自一个或另一个表

This is the query that I am using to match up a members name to an id.

这是我用来将成员名称与id匹配的查询。

SELECT eve_member_list.`characterID` ,eve_member_list.`name` 
FROM `eve_mining_op_members`
INNER JOIN eve_member_list ON eve_mining_op_members.characterID = eve_member_list.characterID
WHERE op_id = '20110821105414-741653460';

My issue is that I have two different member lists, one lists are members that belong to our group and the second list is a list of members that do not belong to our group.

我的问题是我有两个不同的成员列表，一个列表是属于我们组的成员，第二个列表是不属于我们组的成员列表。

How do i write this query so that if a member is not found in the eve_member_list table it will look in the eve_nonmember_member_list table to match the eve_mining_op_members.characterID to the charName

我如何编写此查询，以便如果在eve_member_list表中找不到成员，它将在eve_nonmember_member_list表中查找以将eve_mining_op_members.characterID与charName匹配

I apologize in advance if the question is hard to read as I am not quite sure how to properly ask what it is that I am looking for.

如果问题难以理解，我会事先道歉，因为我不太确定如何正确地询问我在寻找什么。

3 个解决方案

#1

Change your INNER JOIN to a LEFT JOIN and join with both the tables. Use IFNULL to select the name if it appears in the first table, but if it is NULL (because no match was found) then it will use the value found from the second table.

将INNER JOIN更改为LEFT JOIN并加入两个表。如果名称出现在第一个表中，请使用IFNULL选择名称，但如果名称为NULL（因为未找到匹配项），则它将使用从第二个表中找到的值。

SELECT
    characterID,
    IFNULL(eve_member_list.name, eve_nonmember_member_list.charName) AS name
FROM eve_mining_op_members
LEFT JOIN eve_member_list USING (characterID)
LEFT JOIN eve_nonmember_member_list USING (characterID)
WHERE op_id = '20110821105414-741653460';

If you have control of the database design you should also consider if it is possible to redesign your database so that both members and non-members are stored in the same table. You could for example use a boolean to specify whether or not they are members. Or you could create a person table and have information that is only relevant to members stored in a separate memberinfo table with an nullable foreign key from the person table to the memberinfo table. This will make queries relating to both members and non-members easier to write and perform better.

如果您可以控制数据库设计，则还应考虑是否可以重新设计数据库，以便成员和非成员都存储在同一个表中。例如，您可以使用布尔值来指定它们是否是成员。或者，您可以创建一个人员表，并且其信息仅与存储在单独的memberinfo表中的成员相关，并且具有从person表到memberinfo表的可空外键。这将使与成员和非成员相关的查询更容易编写和更好地执行。

#2

You could try a left join on both tables, and then selecting the non-null results from the resulting query -

您可以在两个表上尝试左连接，然后从生成的查询中选择非空结果 -

select * from
(select * from
  eve_mining_op_members as x
  left join eve_member_list  as y1 on x.characterID = y1.characterID
  left join eve_member_list2 as y2 on x.characterID = y2.characterID) as t
where t.name is not null

Or, you could try the same thing with a union and using inner join (assuming joined tables are the same):

或者，您可以使用union并使用内部联接尝试相同的事情（假设连接的表是相同的）：

select * from
(select * from eve_mining_op_members as x
 inner join eve_member_list  as y1 on x.characterID = y1.characterID
  UNION
 select * from eve_mining_op_members as x
 inner join eve_member_list2 as y2 on x.characterID = y2.characterID) as t

You can throw in your op_id condition where you see fit (sorry, I didn't really understand where it came from). Good luck!

你可以在你认为合适的情况下抛出你的op_id条件（对不起，我真的不明白它来自哪里）。祝你好运！

#3

You have several options but by

你有几个选择但是

• using a UNION between the eve_member_list and eve_nonmember_member_list table
• 在eve_member_list和eve_nonmember_member_list表之间使用UNION
• and JOIN the results of this UNION with your original eve_mining_op_members table
• 并使用原始的eve_mining_op_members表加入此UNION的结果

you will get your required results.

您将获得所需的结果。

SQL Statement

SELECT  lst.`characterID` 
        , lst.`name` 
FROM    `eve_mining_op_members` AS m
        INNER JOIN (
          SELECT  characterID
                  , name
          FROM    eve_member_list
          UNION ALL                  
          SELECT  characterID
                  , name
          FROM    eve_nonmember_member_list
        ) AS lst ON lst.characterID = m.characterID
WHERE op_id = '20110821105414-741653460';