Forgot Password,

Lost your password? Please enter your email address. You will receive a link and will create a new password via email.

Have an account?

You must login to ask a question.

Need An Account,

Please briefly explain why you feel this question should be reported.

Please briefly explain why you feel this answer should be reported.

Please briefly explain why you feel this user should be reported.

Search
Ask A Question
Leave An Answer!
Home/ Questions/Q 112420
Next
In Process

databaseanswers.net Latest Questions

  • -2
Clara
Beginner
Asked: 2018-12-15T20:52:33-05:00

How to select records from database table which has to user id (created_by_user, given_to_user) and replace users id by usernames?

This is task table:

task table in a database

This is user table:

user table in a database

I want to select user tasks.
I would give from backend (“given_to_user) id.
But The thing is I want that SELECTED data would have usernames instead of Id which is (created_by_user and given_to_user).

SELECTED table would look like this.
Example:

example data that I need to select

How to achieve what I want?
Or maybe I designed poorly my tables that It is difficult to select data I need? 🙂
task table has to id values that are foreign keys to user table.
I tried many thinks but couldn’t get desired result.

joinselectsqlsqlite

Related Questions

Leave an answer

You must login to add an answer.

Need An Account,

2 Answers

Voted Oldest Recent Random
  1. forpas

    You did not design poorly the tables.
    In fact this is common practice to store the ids that reference columns in other tables.
    You just need to learn to implement joins:

    SELECT 
  task.id, task.title, task.information, user.usename AS created_by, user2.usename AS given_to
FROM 
  (task INNER JOIN user ON task.created_by_user = user.id) 
  INNER JOIN user AS user2 ON task.created_by_user = user2.id;
    • 1
  2. Gordon Linoff

    Do you just want two joins?

    select t.*, uc.username as created_by_username,
       ug.username as given_to_username
from task t left join
     users uc
     on t.created_by_user = uc.id left join
     users ug
     on t.given_to_user = ug.id;

    This uses left join in case one of the user ids is missing.

    • 0