SQL如何根据另一个表构造一个表中两列的查询？

Please take a look at my table structure as below:

请看一下我的表格结构如下:

The data in Area_PrimaryTable and Area_SecondaryTable will be mapped to Table_TableID. I'm wondering how to get the status ID for both table 100 and 111 when I'm querying AreaID = A101?

Area_PrimaryTable和Area_SecondaryTable中的数据将映射到Table_TableID。我想知道当我查询AreaID = A101时如何获取表100和111的状态ID?

Select * from Area where AreaID = A101

UPDATES: I've found my way to build such query but not sure if there is any better way?

更新:我找到了构建此类查询的方法,但不确定是否有更好的方法?

Please advise as below:

请指教如下:

Select * from Table where TableID in
(
    Select PrimaryTable from Area where AreaID = 'A101'
    union
    Select SecondaryTable from Area where AreaID = 'A101'      
)

4 个解决方案

#1

That is the kind of example where JOINs are especially useful. Look that up, since JOINs are something that ends up being used very, very often when working with SQL. They basically allow you to get information from several related tables as part of the same record.

这是JOIN特别有用的例子。仔细看看,因为JOIN最终会在使用SQL时非常频繁地使用。它们基本上允许您从几个相关表中获取信息作为同一记录的一部分。

So there you want to get information from two rows of Table, their relationship being that they are linked to the same row in Area. And I will consider you just want to get both status. That can be solved with the following code :

因此,您希望从两行Table获取信息,它们的关系是它们链接到Area中的同一行。我会认为你只想获得两种身份。这可以通过以下代码解决:

SELECT t1.Status, t2.Status
FROM   Area AS a
  JOIN Table AS t1 ON t1.TableID = a.PrimaryTable
  JOIN Table AS t2 ON t2.TableId = a.SecondaryTable
WHERE AreaID = 'A101'

Note that while using SELECT * is OK in my book when one experiments, I believe it should be avoided in production code in favour of explicitely naming the columns you want to get information from.

请注意,虽然在我的书中使用SELECT *时可以进行实验,但我认为应该避免在生产代码中使用明确命名要从中获取信息的列。

Edit: despite the apparent differences, note that my code proposal has the same behaviour as Xtoxico's. Both proposals are equivalent as far as I know.

编辑:尽管存在明显的差异,请注意我的代码提案与Xtoxico的行为相同。据我所知,这两个提案都是等同的。

#2

Try with this....

试试这个....

SELECT
    t1.status as StatusPrimary, t2.status as StatusSecondary 
FROM
    Area as a, 
    table as t1, 
    table as t2 
WHERE 
    a.primaryTable=t1.TableID and 
    a.secondaryTable=t2.TableId and 
    a.AreaID = A101;

Your Sample return two rows, and my code return in the same row the information

您的Sample返回两行,而我的代码返回同一行的信息

#3

I suggest this one:

我建议这个:

select t.* from table t, area a
where a.AreaID = 'A101' 
and t.TableID in (a.PrimaryTable, a.SecondaryTable)

#4

Try this one

试试这个

SELECT a.AreaID, t.TableID, t.TableName, t.Status FROM table1 t
INNER JOIN (
    Select PrimaryTable, areaId from Area
    union
    Select SecondaryTable, areaID from Area
) a ON t.TableID = a.PrimaryTable order by a.AreaID

Output will look like this (base on your table's structure)

输出将如下所示(基于表的结构)

I hope this is the answer what you looking for, if you only need find Area A101 , you can simply add WHERE AreaID = 'A101' after a ON t.TableID = a.PrimaryTable.

我希望这就是您所寻找的答案,如果您只需要找到A101区域,您可以在ON t.TableID = a.PrimaryTable之后添加WHERE AreaID ='A101'。

EDIT : Sorry missed the point, maybe the query should like this :

编辑:抱歉错过了这一点,也许查询应该是这样的:

SELECT a.AreaID,
   MAX(IF(a.PrimaryTable = t.TableID, t.Status, null)) as T1,
   MAX(IF(a.SecondaryTable = t.TableID, t.status,null)) as T2
FROM Table1 t INNER JOIN Area a ON a.PrimaryTable = t.TableID OR a.SecondaryTable = t.TableID
GROUP BY AreaID