CODEWITHSUNDEEP
google-bigquery
Techie
Techie

Reputation: 829

how to union the tables in big query

select a,b,c from(SELECT max(modifiedtime) a FROM tabl1 ),
(SELECT max(modifiedtime) b FROM tabl2 ),
(SELECT max(modifiedtime) c FROM tabl3  ) ;

working fine but...output is like.. Row 

      a                  b              c    
1   1421906942814      null            null  
2   null               1421906942814   null  
3   null               null            1421906942817

but I want to print data like 

     a            b              c 
1421906942814     1421906942814     1421906942817

how to do it...

Upvotes: 2

Views: 2730

Answers (3)

Mosha Pasumansky
Mosha Pasumansky

Reputation: 14004

I would do CROSS JOIN between them:

SELECT * FROM
  (SELECT max(modifiedtime) FROM tabl1) a
    CROSS JOIN
  (SELECT max(modifiedtime) FROM tabl2) b
    CROSS JOIN
  (SELECT max(modifiedtime) FROM tabl3) c;

Upvotes: 0

Pentium10
Pentium10

Reputation: 207912

As far I know you need to use a dummykey (even if it's a constant) to be able to do the JOIN on it.

This works:

SELECT t1.value,
       t2.value,
       t3.value
FROM
  (SELECT 1 AS dummykey,
          'max1' AS value) t1
JOIN
  (SELECT 1 AS dummykey,
          'max2' AS value) t2 ON t1.dummykey=t2.dummykey
JOIN
  (SELECT 1 AS dummykey,
          'max3' AS value) t3 ON t1.dummykey=t3.dummykey

Upvotes: 1

Adassko
Adassko

Reputation: 5343

try

  SELECT
  (SELECT max(modifiedtime)
   FROM tabl1) a,
  (SELECT max(modifiedtime)
   FROM tabl2) b,
  (SELECT max(modifiedtime)
   FROM tabl3) c;

Upvotes: 0

Related Questions