CODEWITHSUNDEEP
sqlpostgresql
Terry Windwalker
Terry Windwalker

Reputation: 1888

Postgresql: replace parameter in select if data in LEFT JOIN exists

Currently I have two tables as follows:

Invitation Code: id, code, first_name, last_name, email, used, user_id

Users: id, first_name, last_name, email

And what I want to get through SELECT is to Get all invitation codes and users; if the invitation code has user_id (which means a user has used it), then display users' first_name, last_name, email rather than what the invitation code has (replace what is selected by condition); and don't display user again if they have been found by invitation code part. Currently, not all invitation codes are used by a user. For now what I have done is to select Invitation Code and User in two different query, but that way doesn't allow me to build paginator, as I won't know how many people I would get from each table in each page and it would be hard to keep them not repeating themselves.

Any suggestion would be helpful.

Edit:

sample data: Users

[
    {id: 1, first_name: 'User1', last_name: 'New1', email: '[email protected]'},
    {id: 2, first_name: 'User2', last_name: 'New2', email: '[email protected]'}
]

InvitationCode

[
    {id: 1, user_id: 1, first_name: 'invited user1', last_name: 'invited user1', email:'[email protected]', used: true}, 
    {id: 2, user_id: null, first_name: 'invited user2', last_name: 'invited user2', email:'[email protected]', used: false}
]

Ideal Result:

first_name | last_name | email | used | user_id | invitation_code_id 

User1 | New1 | [email protected] | true | 1 | 1

User2 | New2 | [email protected] | null | 2 | null

invited user2 | invited user2 | [email protected] | false | null | 2

Upvotes: 1

Views: 425

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Hmmm . . . I think you want:

select i.id, i.code, coalesce(i.last_name, u.last_name),
       coalesce(i.first_name, u.first_name), 
       coalesce(i.email, u.email),
       i.user_id
from invitation i left join
     user u
     on i.user_id = u.id;

This provides all the invitations, with the fields overridden by the user table.

Upvotes: 1

Related Questions