UNION ALL查询不匹配的表。

I'm using the following query to display dynamic pages, where the page URL's are values stored in two database tables:

我正在使用以下查询来显示动态页面，其中页面URL的值存储在两个数据库表中:

$sql = "SELECT SUM(num) as num FROM (
 SELECT COUNT(URL) AS num FROM gw_geog WHERE URL = :MyURL
 UNION ALL
 SELECT COUNT(URL) AS num FROM gw_world_urls WHERE URL = :MyURL
) AS X";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':MyURL',$MyURL,PDO::PARAM_STR);
$stmt->execute();
$Total = $stmt->fetch();

I'm going to wind up with four or five tables holding more detailed information. I'd like to write separate queries for each table, as each one is a little different. But I first need an intermediate query to get a little information.

最后我会有4到5个表格包含更详细的信息。我想为每个表编写单独的查询，因为每个表都有点不同。但是我首先需要一个中间查询来获取一些信息。

So I tried to transform the following query into another UNION ALL query, but it isn't working.

因此，我尝试将下面的查询转换为另一个UNION ALL查询，但它不起作用。

$stm = $pdo->prepare("SELECT GG.N, GG.IDArea MyID, GG.URL, GG.IDParent
 FROM gw_geog GG
 WHERE GG.URL LIKE :XURL");
$stm->execute(array(
'XURL'=>$XURL
));

Can anyone tell me how to use UNION ALL to query three tables named gw_geog, gw_geog_regions and gw_geog_landforms, where each has fields named URL and IDParent, but only gw_geog has a field named IDArea?

谁能告诉我如何使用UNION ALL来查询gw_geog、gw_geog_regions和gw_geog_landforms这三个表，每个表都有名为URL和IDParent的字段，但只有gw_geog有一个名为IDArea的字段?

Here's what I'd like to do:

下面是我想做的:

1) Get each country's ID (IDArea) and parent (IDParent) from table gw_geog. (I don't need ID's or parents for the other queries.)

1)从表gw_geog中获取每个国家的ID (IDArea)和父ID (IDParent)。(其他查询不需要ID或父母。)

2) Give each row a value based on its source table. For example, the row that includes the value 'russia' is stored in the table gw_geog. So I'd like it to have a value like $Type = 'country.' The rows with the values 'midwest' and 'amazon-river' come from the tables gw_geog_regions and gw_geog_landforms. So I'd like every row from gw_geog_regions to have the value $Type = 'region' and every row from gw_geog_landforms to be $Type = 'landform'.

2)根据源表给每一行一个值。例如，包含值“russia”的行存储在表gw_geog中。我希望它的值是$Type = 'country。'midwest'和'amazon-river'值的行来自gw_geog_regions和gw_geog_landforms。所以我希望gw_geog_regions的每一行都有值$Type = 'region'，而gw_geog_landforms的每一行都是$Type = 'landform'。

If I can get a "Type" (e.g. country, region or landform) for each row, it will help me create a query targeting that category. ID's and parents will also help put together a query for countries and states (table gw_geog).

如果我可以为每一行获取一个“类型”(例如国家、地区或地形)，它将帮助我创建一个针对该类别的查询。ID和父母还将帮助对国家和州(表gw_geog)进行查询。

UPDATE

更新

I've been playing with my new query, but I can't figure out what I'm doing wrong. I've modified my tables a bit, so there a few changes. But it doesn't display any values for $IDParent or $Type X, nor does print_r($row) display anything. Can anyone see what I did wrong?

我一直在玩我的新查询，但我不知道我做错了什么。我对表做了一些修改，因此这里有一些修改。但是它不显示$IDParent或$Type X的任何值，print_r($row)也不显示任何值。有人能看出我做错了什么吗?

$stm = $pdo->prepare("SELECT *
FROM (
 SELECT URL, IDArea, IDParent, 'country' AS TypeX FROM gw_geog
 UNION ALL
 SELECT URL, IDArea, IDParent, 'region' AS TypeX FROM gw_geog_regions
 UNION ALL 
 SELECT URL, IDArea, IDParent, 'landform' AS TypeX FROM gw_geog_landforms
) AS Combined
WHERE Combined.URL LIKE :XURL");
$stm->execute(array(
'XURL'=>$XURL
));

while ($row = $stm->fetch())
{
 $IDParent = $row['IDParent'];
 $TypeX = $row['TypeX'];  
 print_r($row);
}
print_r($row);
echo $TypeX;
echo $IDParent;

2 个解决方案

#1

Your approach of adding a static Type for each is a good idea. It merely needs to be done with single-quoted string literals like

您为每个对象添加静态类型的方法是一个好主意。它只需要用单引号字符串来完成。

SELECT 'region' AS Type

in the SELECT list.

在选择列表中。

To use the tables lacking IDArea, query for NULL in place of that column in the SELECT list:

若要使用缺少IDArea的表，请在SELECT列表中查询NULL以替代该列:

SELECT NULL AS IDArea

It is possible to fill out a UNION query between mismatched tables using combinations of NULLs or string literals to ensure the columns all align.

可以使用null或字符串常量的组合来填充不匹配表之间的联合查询，以确保列都是对齐的。

So your full query would look like

所以你的完整查询应该是这样的

/* Literal string (single-quoted) 'country','region','landform' aliased as `Type` */
SELECT URL, IDArea, IDParent, 'country' AS Type FROM gw_geog
UNION ALL
/* NULL in place of IDArea */
SELECT URL, NULL AS IDArea, IDParent, 'region' AS Type FROM gw_geog_regions
UNION ALL 
/* NULL in place of IDArea */
SELECT URL NULL AS IDArea, IDParent, 'landform' AS Type FROM gw_geog_landforms

Applying the WHERE clause can be done on the outside if you enclose it all as a subquery:

如果将WHERE子句作为子查询括起来，则可以在外部执行:

SELECT *
FROM (
    SELECT URL, IDArea, IDParent, 'country' AS Type FROM gw_geog
    UNION ALL
    SELECT URL, NULL AS IDArea, IDParent, 'region' AS Type FROM gw_geog_regions
    UNION ALL 
    SELECT URL NULL AS IDArea, IDParent, 'landform' AS Type FROM gw_geog_landforms
) AS combined
WHERE combined.URL LIKE :XURL

#2

The textbook answer is to select A, B, C from the tables that have those columns and select A, B, NULL from the tables that are missing one. Considering making a view that implements the union so you can keep the business logic simple.

教科书上的答案是，从有这些列的表中选择A、B、C，并从缺失1的表中选择A、B、NULL。考虑创建实现union的视图，以便使业务逻辑保持简单。

#1