SQL JOIN Question

Databases
1

I only have rudimentary SQL knowledge… This is likely simple for some of you, but nothing I have tried so far has worked.

If it’s matters this is using PostgreSQL

I’m trying to do a Select to get all the human readable IDs, from 2 related tables, where one of them is self joined, that I need to display in a listbox :

Here is the truncated Table setup (only the relevant fields)

CREATE TABLE IF NOT EXISTS UserTable(
	Key0            SERIAL PRIMARY KEY,
	LoginName		TEXT NOT NULL);
------------------------------------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS Department( 
	Key0             		SERIAL PRIMARY KEY,
	ID				        VarChar(6) Unique NOT NULL,
	Name			        TEXT NOT NULL,
	ManagerKey			    Integer   REFERENCES UserTable(Key0),
	ReportsToKey			Integer  REFERENCES Department(Key0);
------------------------------------------------------------------------------------------------------------------

I am creating list for all departments

As you can see Department has a reference to itself (a Self Join) as well as to the UserTable

DeptID | Dept Name | Manager Name | ReportsToDept

The Human readable manager Name has to come from the UserTable and the ReportsTo Dept (DeptID) has to come from a different record in teh Department table.

I can get either one of the human readable fields with the the other fields i need from each Department record as shown below:

Select A.ID, A.Name As DeptName, C.loginname AS ManagerName From Department As A Left Join UserTable As C ON A.ManagerKey = C.key0

That gets me manager name…

And

Select A.ID, A.Name As DeptName, B.ID AS ReportsToDept From Department As A Left Join Department As B ON A.ReportsToKey = B.key0

Tgives me the Department ID that this one reports to.

Doing either one is pretty straightforward… but I can’t figure out how to get both in a single select!

Any help would be appreciated!

Thanks,
-karen

2

Add the Left Join from the second statement to the first statement, adjust the columns you’re selecting, and you’re there. (Hard to give you an example on my iPhone.)

3

From
table as a
left join othertable as b
left join table as c

4

Thanks Kem… I had actually never done a 3 way join before! (usually only 2 tables)

-Karen

5

Simply combine them. And drop the cryptic table aliases that you don’t really need.

SELECT
ID AS DeptID, 
Name AS DeptName, 
UserTable.LoginName AS ManagerName,
ReportsTo.Name AS ReportsToDept
FROM Department
LEFT JOIN UserTable ON UserTable.ID = Department.ManagerKey
LEFT JOIN Department AS ReportsTo ON ReportsTo.ID = Department.ReportsToKey
6

Thanks Tim, Pretty much what i finally did. (it was actually for a view)

Why the short aliases is because just like API 2, SQL, with longer names is just too verbose for me to easily spot errors or understand. Too much text!

BTW another thing that was tripping me up is that I modified some of those tables in Valentina Studio, and by default it quotes all field names in the SQL it generates, so they become case sensitive… and I did not mach the case exactly!!! That cost me a lot of time!

Mixed case is easier to read, but case typos can be hard for me to spot, so I don’t want case sensitive field names!

-karen

7

use_snake_case_instead

2 Likes
8

Yeah, I use lowercase for everything in the database.

9

I see Kem is a fellow Slytherin(using Snake case :snake: ), at least when it comes to picking a database naming convention.

1 Like
10

Although solved, SQL CTE’s are often best suited for hierarchical queries.
Happy to provide more detail if you need it.

Ian

11

SQL CTE is brilliant!!!

1 Like