0

I want to join three tables respectively from the below SQLFiddle

http://sqlfiddle.com/#!9/5dd558/4

Now I want to create one table from this table based on date and Brand. Like, I want data in this manner

Date, Brand, Series, Table_1_Viewers, Table_2_Viewers, Table_2_Viewers

and if data is not matched on the table then the field should be nulled.

What I have done 

SELECT h.*, 
   a.`amazon_viewers` AS "Table_1_Viewers", 
   n.`views`          AS "Table_2_Viewers", 
FROM   `Table-1` h 
   LEFT JOIN `Table-2` a 
          ON h.`date` = a.`date` 
             AND h.`brand` = a.`brand` 
   LEFT JOIN `Table-3` n 
          ON h.`date` = n.`date` 
             AND h.`brand` = n.`brand`

Obviously I am selecting data from table-1 so it will display brand column only from table-1 but how can I get all table's brand column name in one column and merge these tables.??

The output I want...

|    Date    |   Brand  |     Series     | Table_1_Viewers | Table_2_Viewers | Table_3_Viewers |
|:----------:|:--------:|:--------------:|:---------------:|:---------------:|:---------------:|
|  12/1/2018 |   TEST   |   TEST_SERIES  |       100       |                 |                 |
| 10/15/2018 |    MTV   |       GOT      |       1000      |                 |       1000      |
|  12/1/2018 |   TEST   |     Viking     |      485632     |      856325     |                 |
|  12/1/2018 |   TEST   | Another Series |                 |       200       |                 |
| 10/15/2018 |   POGO   |       GOT      |                 |       1000      |                 |
|  7/1/2019  | No_Match |   TEST_SERIES  |                 |                 |       100       |
|  12/1/2018 |  TEST-5  |     Viking     |                 |                 |      953022     |
Rohit Chauhan
  • 1,119
  • 1
  • 12
  • 30
  • 1
    If you want to join pictures, use photoshop. If you want to join tables, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry Dec 30 '19 at 09:59
  • Thank you, Strawberry for posting this link, I wasn't aware of this, please check the updated question. – Rohit Chauhan Dec 30 '19 at 10:29
  • 1
    Please put everything needed for your question in the question as text--not images--not just at a fiddle link. Please give all of a [mre]--including example desired input & output. – philipxy Dec 30 '19 at 10:38
  • the output table is big and as suggested by Strawberry I can't add ASCII text properly. – Rohit Chauhan Dec 30 '19 at 10:40
  • Cut & paste or use OCR (free online & on Windows) or type it. https://meta.stackoverflow.com/q/285551/3404097 Use minimal data for examples. – philipxy Dec 30 '19 at 10:41

1 Answers1

1

You can do union all with aggregation :

select t.Date, t.Brand, t.Series_name,
       sum(case when table_view = 't1' then amazone_viewer else 0 end) as Table_1_Viewers,
       . . .
from (select h.date, h.brand, h.series_name, h.amazone_viewer, 't1' as table_view
      from `Table-1` h union all
      select h1.date, h1.brand, h1.series, h1.viewes, 't2'
      from `Table-2` h1 union all
      . . .
    ) t
group by t.Date, t.Brand, t.Series_name;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52