sql replace multiple IDs with strings from another table

Question

I have the SQLite database containing four arrays:

CREATE TABLE IF NOT EXISTS EVENTS_LIST
(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
DATE TEXT,
WORKER INTEGER,
EVENT INTEGER,
REGISTRATION_METHOD INTEGER)

CREATE TABLE IF NOT EXISTS WORKERS
(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
WORKER TEXT)

CREATE TABLE IF NOT EXISTS EVENTS
(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
EVENT TEXT)

CREATE TABLE IF NOT EXISTS REGISTRATION
(ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
REGISTRATION_METHOD TEXT)

I need to get the following ID's: Worker, Event and Registration replaced with the name attached to this ID in the appropriate table.

Example:

3 Workers: 0001) John 0002) Tom 0003) Mike
3 Types of registration: 0) PIN 1) Fingerprint 2) NFC Card
3 Types of events: 1) came in 2) came out 3) came out on business

I get:

DATE | WORKER | EVENT | REGISTRATION
XXXX |  0001  |   1   |      0
XXXX |  0003  |   2   |      1

I need:

DATE | WORKER |    EVENT   | REGISTRATION
XXXX |  John  |   came in  |      PIN
XXXX |  Mike  |   came out |  Fingerprint

I found those solutions:

How to replace fetched values with another column while querying with SQL Server 2008 R2

Multiple column SQL joins in a table

The first link is very similar but related only to one column and the second link is more complicated but has a few "LEFT OUTER JOIN" commands which seems to be the good direction.

Can anybody give me directions on how to accomplish this?

score 0 · Answer 1 · answered Jun 01 '14 at 19:36

0

Use this code:

select DATE=el.DATE,
   WORKER=w.wORKER,
   EVENT=e.EVENT,
   REGISTRATION=r.REGISTRATION
   from
   IF NOT EXISTS EVENTS_LIST as el 
   join 
   IF NOT EXISTS WORKERS as w on el.WORKER=w.ID
   join 
   IF NOT EXISTS EVENTS as e on el.EVENT=e.ID
   join
   IF NOT EXISTS REGISTRATION as r on el.REGISTRATION_METHOD=r.ID

answered Jun 01 '14 at 19:36

Gaurav Kumar Singh

582
2
5
14

Thank you very much for the code. I will try it. Do I really have to include "IF NOT EXISTS" in the SQL query? – user3156980 Jun 01 '14 at 19:46
I tried your code. However, there is a problem: **Warning: SQLite3::query(): Unable to prepare statement: 1, ambiguous column name: WORKER** The problem is the same for EVENT. It is quite interesting because REGISTRATION=R.REGISTRATION_METHOD and JOIN REGISTRATION AS R ON el.REGISTRATION=R.ID results in all registration IDs changed to 0. After I remove JOIN statements and =XXXX statements, only IDs are displayed. There may be a conflict between JOINs and =XXXX. I also tried to change the WORKERS.WORKER column name to W_WORKER, the error message is the same. Could you help me? – user3156980 Jun 04 '14 at 05:27
You can post your code as a new answer instead of editing this one. – bjb568 Jun 04 '14 at 05:53
Isn't it strange to accept my own reply if I present the solution that is working for me? – user3156980 Jun 05 '14 at 12:12

score 0 · Accepted Answer · answered Jun 05 '14 at 12:10

This code is working for me:

select el.DATE,
   w.wORKER,
   e.EVENT,
   r.REGISTRATION
   from
   EVENTS_LIST as el 
   join 
   WORKERS as w on el.WORKER=w.ID
   join 
   EVENTS as e on el.EVENT=e.ID
   join
   REGISTRATION as r on el.REGISTRATION_METHOD=r.ID

Thanks!

sql replace multiple IDs with strings from another table

2 Answers2