在执行UNION时应用WHERE子句

I am struggling to get this MySQL query to work. I have two tables:

我正在努力让这个MySQL查询工作。我有两张桌子:

Table 1

TYPE | NAME | LAT | LON | ICON

Table 2

ID | UID | NAME | LAT | LON | ICON

I am trying to select all results from Table 1 and only select some results from Table 2. I am trying to apply a WHERE clause to Table 2 but it doesn't seem to work.

我试图从表1中选择所有结果,只从表2中选择一些结果。我正在尝试将WHERE子句应用于表2,但它似乎不起作用。

I read the documentation and it said for a UNION to work the number of columns have to be the same. How can I then only select the same number of columns from both tables to be returned but filter the second table by a column only found on that table?

我阅读了文档,它说UNION工作的列数必须相同。那么我怎样才能从两个表中选择相同数量的列来返回,但是只能通过该表上的列过滤第二个表?

My (pseudo)Query:

(SELECT name,lat,lon,icon 
FROM Table1)

UNION 

(SELECT name,lat,lon,icon 
FROM Table2
WHERE uid ="1")

1 个解决方案

#1

SELECT * FROM table1
UNION
SELECT
    NULL AS `type`,
    `name`,
    `lat`,
    `long`,
    `icon`
FROM table2 WHERE uid = 1

http://www.sqlfiddle.com/#!9/0a942/8

This selects everything from table1, and only where uid = 1 from table2.

这将从table1中选择所有内容,并且仅从table2中选择uid = 1。

An UNION can only be performed if both row sets have the exact same columns. Since there is no type column in table2, we select a NULL and name it type so we can do the UNION.

只有两个行集具有完全相同的列时才能执行UNION。由于table2中没有类型列,因此我们选择NULL并将其命名为类型,以便我们可以执行UNION。

#1

SELECT * FROM table1
UNION
SELECT
    NULL AS `type`,
    `name`,
    `lat`,
    `long`,
    `icon`
FROM table2 WHERE uid = 1