CODEWITHSUNDEEP
sqlsqlite
mindrunner
mindrunner

Reputation: 176

SQLITE: How to select a column value based on different columns in another table

I apologise in advance because I have no idea how to structure this question. I have the following tables:

Sessions:
+----------+---------+
| login    | host    |
+----------+---------+
| breilly  | node001 |
+----------+---------+
| pparker  | node003 |
+----------+---------+
| jjameson | node004 |
+----------+---------+
| jjameson | node012 |
+----------+---------+

Userlist:
+----------+----------------+------------------+
| login    | primary_server | secondary_server |
+----------+----------------+------------------+
| breilly  | node001        | node010          |
+----------+----------------+------------------+
| pparker  | node002        | node003          |
+----------+----------------+------------------+
| jjameson | node003        | node004          |
+----------+----------------+------------------+

What kind of SQL query should I perform so I can get a table like this?:

+----------+---------+------------+
| login    | Host    | Server     |
+----------+---------+------------+
| jjameson | node004 | Secondary  |
+----------+---------+------------+
| jjameson | node012 | Wrong Node |
+----------+---------+------------+
| pparker  | node003 | Secondary  |
+----------+---------+------------+
| breilly  | node001 | Primary    |
+----------+---------+------------+

Currently I'm just using Go with a bunch of structs / hashmaps to generate this. I am planning to migrate the users / sessions to an in memory sqlite Database, but I can't seem to wrap my head around a query to get this sort of table.

The Server column is based on whether the user is logged on his primary / secondary or wrong machine.

I've put this in SQL Fiddle as well

Upvotes: 1

Views: 1272

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Use case logic:

select s.*,
       (case when s.host = ul.primary_server then 'primary'
             when s.host = ul.secondary_server then 'secondary'
             else 'wrong node'
        end) as server
from sessions s left join
     userlist ul
     on s.login = ul.login;

Upvotes: 3

Related Questions