1

I want to join two tables on Tableau. I don't want to lose any entries so I'm using a full outer join:

The data looks like this

Table 1:

Name1         Status     Storage   Certificate
gbo001     Running         16GB         on
gbo003     Running         16GB         on
gbo005     Running         16GB         on
gbo006     Running         16GB         on

Table2

Name2      Price
gbo001     10000
gbo002     12000
gbo003     12000 
gbo004     16000
gbo006     11000
gbo007     14000

So I am using a full outer join, this is my query:

SELECT "Table_1"."name1" AS "name1",
  "Table_1"."Status" AS "Status",
  "Table_1"."Storage" AS "Storage",
  "Table_1"."Certificate" AS "Certificate",
  "Table_2"."Name2" AS "Name2",
  "Table_2"."Price" AS "Price",
 FROM "public"."Table1" "Table1"
  FULL JOIN "public"."Table2" "Table2" ON ("Table1"."Name1" = "Table2"."Name2")

Strangely enough it is giving me same results as left join would:

Name1         Status     Storage   Certificate   Name2     Price
gbo001     Running         16GB         on       gbo001     10000
gbo003     Running         16GB         on       gbo001     12000
gbo005     Running         16GB         on       null       null
gbo006     Running         16GB         on       gbo006     11000

And these are the results I'd be expecting with a full outer join:

Name1         Status     Storage   Certificate     Name2       Price
gbo001     Running         16GB         on         gbo001      10000
 null       null           null        null        gbo002      12000 
gbo003     Running         16GB         on         gbo003      12000
 null       null           null        null        gbo004      16000
gbo005     Running         16GB         on         null        null 
gbo006     Running         16GB         on         gbo006      11000
 null       null           null        null        gbo007      14000

Is it possible to adjust my query accordingly so I can see all existing entries from both tables?

Lina Linutina
  • 363
  • 1
  • 2
  • 17
  • My tables come from Amazon Redshift. – Lina Linutina Nov 03 '17 at 12:31
  • The query looks correct to me. Maybe a bug in Redshift. Is that really your **complete** query? Did you hide a `where` clause from us maybe? –  Nov 03 '17 at 12:41
  • Yes, this is a complete query and there's no where clause in this case. – Lina Linutina Nov 03 '17 at 12:59
  • 1
    How are you joining the tables in tableau. Is it possible to not use custom SQL and use the built in joins? – tyvich Nov 03 '17 at 14:28
  • Precisely, I am using a built in join. This is it converted to a custom SQL query. Already found a problem that is duplications of names in both tables (the dataset is really big), so first need to do some changes on Redshift. Well, I suppose it's this problem. – Lina Linutina Nov 06 '17 at 10:21

1 Answers1

0

In case somebody is having a similar problem on Tableau, this happened to me a few days ago and after several hours trying to figure out why tableau was creating a left join instead of a full join I decided to just close and reopen Tableau and that actually worked. So I guess there's some kind of bug around this.

Hope this saves someone some time.