SQL help

General
1

I am trying to do some SQL to create a VIEW in SQLite (but could be also in PostgreSQL on the second version of the app). And this is just beyond my SQL knowledge and my google-fu to resolve this is failing.

table 1: person

UUID as string  (Unique UID that is 32+ characters in length)
name as string  (this is the person's name)
...

table 2: relationshipType

UUID as string (Unique UID that is 32+ characters in length) name as string (this is the relationship name - Supervisors, Manager, etc) ...

table 3: supervisorRelationships

UUID as string (Unique UID that is 32+ characters in length)
boss_UUID as string  (UUID from person table)
employee_UUID as string  (UUID from person table)
relationship_UUID as string  (UUID from relationshipType table)
...

now I want to create a VIEW that has the following information

UUID as string  (UUID from table 3)
bossName as string  (name field from table 1 where table 3.boss_UUID = table 1.UUID)
employeeName as string  (name field from table 1 where table 3.employee_UUID = table 1.UUID)
relationshipName as string (name field from table 2 where table 3.relationship_UUID = table 2.UUID)

but whenever I try to create the SQL queries I am failing left and right. it is beyond my skills set.

can anyone give me a clue how to do this?
thanks
sb

2

and the tables have a lot more columns but I striped down the information to make it easier to discuss here.

3

This should do it for you (untested of course).

SELECT 3.uuid, 1.bossname, 4.employeename, 2.relationshipname FROM 3 INNER JOIN 1 ON 1.uuid = 3.boss_uuid INNER JOIN 2 ON 2.uuid = 3.relationship_uuid INNER JOIN 1 AS 4 ON 4.uuid = 3.employee_uuid;

As you are connecting to table 1 twice you need to use an alias for the second connection (1 AS 4).

HTH

4

@Wayne Golding that is perfect. It works without tweaking at all. I need to learn how to do the INNER JOINS and how they stack up.

Thanks!

5

Select * from table1
Join table2 on …

Outer join (the default) returns all rows from table1 and provides nulls for table2 if there are no matching rows. Inner join returns only those rows in table1 where there are matching rows in table2.