How to join to a table multiple times?

JunkDoom
JunkDoom Member
edited March 2023 in Datasets

I'm struggling to replicate a simple type of join my company does all the time in SQL Server within a Domo DataSet view. Perhaps this isn't possible to do yet. I'm hoping someone can help out.

Attached photo which has most of the complexity removed, but shows what I'm trying to do. We see "Transactions" in one table, and "Transaction_Contacts" (the buyer and seller involved in each transaction) in the other. Each transaction has 1 buyer and 1 seller.

In SQL Server, I use "table aliases" to JOIN to the Transaction_Contacts table TWICE. I use "column aliases" to rename the output columns, to become "SellerName" or "BuyerName" as appropriate. The output shown at the bottom is what the customers are expecting to see.

This is easy in SQL Server, as shown in the sample code at the bottom. When I try to create a Domo Dataset View, I can join the "Transaction_Contacts" table once, but if I try to add it again. it's greyed out/disabled. Does anyone know why? Is there a better way to do this? (I'm sure I could create an ETL but would like to avoid the complexity if possible due to understaffing.

Domo_Join_To_Table_Twice.png


Tagged:

Answers

  • Jones01
    Jones01 Contributor

    You could create two contacts views. One filtered by buyers and one by sellers then join them both to your main dataset

    I'm quite new to domo so someone may have a better way.

  • RobSomers
    RobSomers Coach

    If you want to do this just using views, then I think @Jones01 has the right idea. Depending on how many joins you want to do, this might get just as complex as just using an ETL. You could try doing this using a SQL dataflow as that might provide you with a little more familiarity with the process, but depending on the number and complexity of the joins, that could take a long time to run.

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • JunkDoom
    JunkDoom Member

    I am able to create multiple views of the same dataset, such as "View_Sellers" and "View Buyers", then join that to Transactions. Only issue there is that Domo doesn't distinguish which field is from which table. It just appends a number to the column name automatically. For example, "First Name", "Last Name", Contact ID", then "First Name 1", "Last Name 1", "Contact ID 1".

    To get around this, In "View Sellers" and "View Buyers" view, I have renamed all columns with a prefix, such as "Seller_First_Name", "Buyer_First_Name", etc. It's messy, but I think it will work until Domo allows multiple joins to the same table.

  • ST_-Superman-_
    ST_-Superman-_ Coach

    I would encourage you to explore some of the other transform tools that Domo offers. Not all problems can be solved with the same tool. In this case, if you join the data with either an ETL or SQL dataflow, you will have much more control over the join. The join mechanic for dataset views is meant for very simple joins.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Categories