i've got this query:
我有这个问题：
SELECT * FROM (
(SELECT ACCOUNTS.INSTALLTIME, ACCOUNTS.HONAME, ACCOUNTS.ADDRESS, ACCOUNTS.CITY, ACCOUNTS.STATE, ACCOUNTS.ZIP, ACCOUNTS.JOBNUMBER, ACCOUNTS.INSTALLDATE, ACCOUNTS.RESULT, ACCOUNTS.NOTES, ACCOUNTS.SMNOTES, technicians.technumber, technicians.boardplacing
FROM ACCOUNTS
INNER JOIN technicians ON ACCOUNTS.INSTALLER = technicians.technumber)
UNION
(SELECT service.servicetime, service.Customername, service.address, service.city, service.state, service.zip, service.ID, service.serviceday, service.result, service.servicenotes, service.board, technicians.technumber, technicians.boardplacing
FROM service
INNER JOIN technicians ON service.technician= technicians.technumber)
) as t WHERE t.INSTALLDATE = '$date' ORDER BY t.boardplacing
is there any way I can make a query similar to:
我有什么方法可以进行类似的查询：
SELECT * FROM (
(SELECT ACCOUNTS.INSTALLTIME, ACCOUNTS.HONAME, ACCOUNTS.ADDRESS, ACCOUNTS.CITY, ACCOUNTS.STATE, ACCOUNTS.ZIP, ACCOUNTS.JOBNUMBER, ACCOUNTS.INSTALLDATE, ACCOUNTS.RESULT, ACCOUNTS.NOTES, ACCOUNTS.SMNOTES, '' as priority, ACCOUNTS.PAFS, ACCOUNTS.upsell, ACCOUNTS.TERM, ACCOUNTS.MMRUPGRADE, ACCOUNTS.WARRANTY, ACCOUNTS.EFT, technicians.technumber, technicians.boardplacing
FROM ACCOUNTS
INNER JOIN technicians ON ACCOUNTS.INSTALLER = technicians.technumber)
UNION
(SELECT service.servicetime, service.Customername, service.address, service.city, service.state, service.zip, service.ID, service.serviceday, service.result, service.servicenotes, service.board, '', '', '', '', '', '', technicians.technumber, technicians.boardplacing
FROM service
INNER JOIN technicians ON service.technician= technicians.technumber)
) as t WHERE t.INSTALLDATE = '$date' ORDER BY t.boardplacing
basically i need fake columns in my union. is there any way to pull that off with the joins? is there some other better way to do this?
基本上我需要在我的工会中使用假柱。有什么办法可以用连接来解决这个问题吗？有没有其他更好的方法来做到这一点？
1 个解决方案
#1
4
When I am writing a union query and need "fake" or "dummy" columns, I just use:
当我编写联合查询并需要“假”或“虚拟”列时，我只使用：
NULL AS Fake
NULL假冒
So the whole query will look something like this;
所以整个查询看起来像这样;
SELECT A.CHEESE, A.BREAD, A.GARLIC, A.COST
FROM ACHEESETABLE A
WHERE A.BREAD = WHEAT
UNION ALL
SELECT NULL AS CHEESE, B.BREAD, NULL AS GARLIC, B.COST
FROM BCHEESYTABLE B
WHERE B.COST > 15
This way both queries have a CHEESE and GARLIC column, but BCHEESYTABLE does not contain the columns, CHEESE or GARLIC. Doing it like this also allows different WHERE criteria for both queries, so in essence it is possible to have two different record population in the same query.
这样两个查询都有一个CHEESE和GARLIC列，但是BCHEESYTABLE不包含CHEESE或GARLIC列。这样做也允许两个查询具有不同的WHERE标准，因此实质上可以在同一查询中具有两个不同的记录填充。
#1
4
When I am writing a union query and need "fake" or "dummy" columns, I just use:
当我编写联合查询并需要“假”或“虚拟”列时，我只使用：
NULL AS Fake
NULL假冒
So the whole query will look something like this;
所以整个查询看起来像这样;
SELECT A.CHEESE, A.BREAD, A.GARLIC, A.COST
FROM ACHEESETABLE A
WHERE A.BREAD = WHEAT
UNION ALL
SELECT NULL AS CHEESE, B.BREAD, NULL AS GARLIC, B.COST
FROM BCHEESYTABLE B
WHERE B.COST > 15
This way both queries have a CHEESE and GARLIC column, but BCHEESYTABLE does not contain the columns, CHEESE or GARLIC. Doing it like this also allows different WHERE criteria for both queries, so in essence it is possible to have two different record population in the same query.
这样两个查询都有一个CHEESE和GARLIC列，但是BCHEESYTABLE不包含CHEESE或GARLIC列。这样做也允许两个查询具有不同的WHERE标准，因此实质上可以在同一查询中具有两个不同的记录填充。