CODEWITHSUNDEEP
mysql
thinking_hydrogen
thinking_hydrogen

Reputation: 189

Can the following be achieved using MYSQL union?

We have a Web Analytics database with 2 tables Table 1 and Table 2 as seen below. enter image description here

We really could do with some inputs here to display the desired result as a single row.

Many Thanks

X

Upvotes: 1

Views: 66

Answers (1)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

SELECT
    A.Name,
    A.ref_num Policy_number,
    B.QNum Quote_number
FROM
    (
        SELECT AA.Name,BB.ref_num FROM
        (SELECT Name,ref_num FROM Table1 WHERE Name='Purchase' AND Cookieid=123456) AA
        INNER JOIN Table2 BB USING (Reference)
    ) A,
    (
        SELECT BB.ref_num QNum FROM
        (SELECT * FROM Table1 WHERE Name='Quote' AND Cookieid=123456) AA
        INNER JOIN Table2 BB USING (Reference)
    ) B
;

All you have to do is set the Cookieid in both subqueries.

Make sure you have this index:

ALTER TABLE Table1 ADD INDEX Cookieid_Name_ndx (Cookieid,Name);

Upvotes: 1

Related Questions