Join Select Query with a Pivot Query

问题: I have been unsuccessful in creating an inner join between a select query and a pivot query. I’m receiving a syntax error near the join which I’m unable to resolve. I’m usi...

问题:

I have been unsuccessful in creating an inner join between a select query and a pivot query. I’m receiving a syntax error near the join which I’m unable to resolve. I’m using MySQL and I'm new to nested queries.

Tbl_1 
ID  FirstName   LastName    Company
123 Dave        Jones       Ajax Inc
567 Mike        Smith       Beta Corp
891 Jane        Allan       Allan Consulting

Tbl_2
ID  fname   ftext
123 Email   dave.jones@ajax.com
567 Email   mike.smith@beta.com
567 Phone   6469612351
891 Email   jane.allan@gmail.com
891 Phone   4086089414
891 Cell    4086852918

Tbl_1 Select Query

SELECT firstname, lastname, company, id 
FROM   Tbl_1 
ORDER BY lastname, firstname 

Table 2 Pivot Query

SELECT id, 
   Max(IF(fname = 'Email', ftext, NULL)) AS 'Work Email', 
   Max(IF(fname = 'Phone', ftext, NULL)) AS 'Work Phone', 
   Max(IF(fname = 'Cell', ftext, NULL))  AS 'Cell Phone' 
FROM   Tbl_2 
GROUP  BY id; 

[Desired Output][1]

回答1:

You need to join the tables and group by all Fields that have no aggregate function like MAX, SUM,...

SELECT Tbl_1.FirstName
    ,Tbl_1.LastName
    ,Tbl_1.Company
    ,Tbl_1.ID
    ,MAX(IF(Tbl_2.fname = 'Email', Tbl_2.ftext, NULL)) AS 'Work Email'
    ,MAX(IF(Tbl_2.fname = 'Phone', Tbl_2.ftext, NULL)) AS 'Work Phone' 
    ,MAX(IF(Tbl_2.fname = 'Cell', Tbl_2.ftext, NULL))  AS 'Cell Phone' 
FROM Tbl_1
INNER JOIN Tbl_2 on Tbl_1.ID = Tbl_1.ID

GROUP BY
    Tbl_1.FirstName
    ,Tbl_1.LastName
    ,Tbl_1.Company
    ,Tbl_1.ID

回答2:

Sorry Dave but I didn't catch your desired output but if I have to guess, you may want to try making both of your query above as a subquery then LEFT JOIN it:

SELECT * FROM
(SELECT firstname, lastname, company, id 
FROM  Tbl_1 
ORDER BY lastname, firstname) t1 LEFT JOIN
(SELECT id, 
   MAX(IF(fname = 'Email', ftext, NULL)) AS 'Work Email', 
   MAX(IF(fname = 'Phone', ftext, NULL)) AS 'Work Phone', 
   MAX(IF(fname = 'Cell', ftext, NULL))  AS 'Cell Phone' 
FROM Tbl_2 
GROUP BY id) t2 ON t1.id=t2.id;

回答3:

Thank you for pointing me in the right direction. The following abstraction is what I actually used.

SELECT     tbl_1.id,
           tbl_1.firstname,
           tbl_1.lastname,
           tbl_1.company,
           max(IF(tbl_2.fname = 'Email', tbl_2.ftext, NULL)) AS 'Work Email',
           max(IF(tbl_2.fname = 'Phone', tbl_2.ftext, NULL)) AS 'Work Phone',
           max(IF(tbl_2.fname = 'Cell', tbl_2.ftext, NULL))  AS 'Cell Phone',
FROM       tbl_1
INNER JOIN tbl_2
ON         tbl_1.id = tbl_2.id
GROUP BY   tbl_1.id,
           tbl_1.firstname,
           tbl_1.lastname,
           tbl_1.company;
  • 发表于 2019-01-19 05:16
  • 阅读 ( 190 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除