Display value from chain of foreign keys

Get Help
1

Sorry for the week subject. Struggling to wrap my head around what I’m actually asking. Gonna do my best to describe what I’m trying to do. Just installed Metabase and still trying to wrap my head around the terminology and such.

For this question I have three tables.

Users

  • ID
  • First Name
  • Last Name
  • Email
  • User Name

Employees

  • Id
  • User Id => Users:Id
  • Start Date

Work Orders

  • Start Date
  • Title
  • Sales Person => Employees:Id
  • Amount (price)

I’m trying to design a question that shows how much each employee has sold. I’ve easily created a table that sums the amount column and groups it by Sales person. However, I would like to show the First and Last name of that employee from its related entry in contacts. I can have it display the contact ID (from the employee table) but struggling to get the names to show. Is this something Im going to have to do a custom SQL query for?

Thanks for your help!

2

I’d recommend it! That’s a perfectly good example to try your hand at the underlying SQL. You’ll just need a couple inner joins to make it happen. You’ll specify the name of the table you want to join, and then the primary / foreign keys within the FROM:

inner join employees on employees.id = [your_table].employees_id
inner join users on users.id = employees.user_id

You can refer to the columns you’d like in the report within the SELECT clause, so you might have something like:

select users.First_Name, users.Last_Name, [total_sales]

Good luck!

2 Likes
3

If you don’t fancy using SQL, just group by the columns required.

1 Like
4

Thanks guys! I’ll write some queries :slight_smile: I was hoping to avoid having to do that to make it easy for the boss to build questions on his own. Oh well! Thanks for the help! :smiley:

5

If you have the database rights, create some views. If you can flatten the schema a little, you should rarely need to use SQL.

6

This is 100% a reasonable suggestion. Admittedly, I default to just writing SQL since I know it. I have totally come across dashboards, though, where it would be reasonable to create a (materialized, probably) view – and for user delight, I probably should just do it. But you know, those are never the projects that just bubble up to the top of the priority list :stuck_out_tongue:

7

I’ve never worked with Views before. I suppose now is as good a time as ever! : )

1 Like
8

I totally get that about things bubbling to the top. This is already an incredible program and I’m so excited to be using this and not have to write tons of code to analyze my data. This program is seriously a huge time/job saver! Very thankful the the product as is! :slight_smile: