CODEWITHSUNDEEP
sqlpostgresql
Charlie
Charlie

Reputation: 85

SQL comparing each row to a reference row

I am trying to compare each row from a select statement to a reference row.

So to put it into context I would like to find the reference row which is the account details for one of our users. 

SELECT id, first_name
FROM account
WHERE id = '100'

Would return the info for the user in question

Then I want to run a SELECT statement to return all users - pretty straightforward

SELECT id,first_name
FROM account

For each row I would like to compare the first_name with the reference row. If it is the same return a '1' if it is different return a '0'

I can do this if I type in the value to compare e.g 'Paul'

SELECT id,first_name,
CASE
    WHEN first_name = 'Paul' THEN '1'
    ELSE '0'
END
FROM account

But obviously I want to replace Paul with whatever the first_name is from the reference row above.

My googling suggests I need to declare a variable and then something with SELECT INTO a variable

DO $$;
Declare
       @reference_first_name   text;
BEGIN
SELECT first_name
into @reference_first_name
FROM account
WHERE id = ‘100’
END;

But I can't seem to put it together.

Then to go a step further would it be possible to reference multiple columns?

Upvotes: 2

Views: 56

Answers (3)

sgeddes
sgeddes

Reputation: 62831

Here's another option using an outer join:

select a.id, a.first_name, case when a.first_name = b.first_name then 1 else 0 end
from account a
    left join account b on b.id = 100

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65313

Use correlated subquery as :

select a.id, a.first_name,
        ( select count(*) 
            from personnel p 
           where p.id = 100 
             and upper(p.first_name) = upper(a.first_name) ) as flag
  from account a;

provided you have a table called personnel and has an ID fixed for all comparisons.

Rextester Demo

Upvotes: 0

S-Man
S-Man

Reputation: 23686

You could do this simply in a subquery:

demo:db<>fiddle

SELECT 
    id, 
    first_name, 
    CASE 
        WHEN (SELECT first_name FROM users WHERE id = 100) = first_name THEN 1 
        ELSE 0
    END
FROM users;

Other ways are using a CTE or a JOIN (see fiddle for these versions)

Upvotes: 1

Related Questions