Join columns to search string-CodePudding

I have these tables which I would like to query:

create table employees
(
    id bigint generated by default as identity (maxvalue 2147483647),
    username varchar(100) not null,
    password varchar(60) not null,
    account_id bigint,
    role_id bigint,
    first_name varchar(150),
    last_name varchar(150),
    primary key (id)
);

create table accounts
(
    id bigint generated by default as identity,
    account_name varchar(150) not null,
    account_group_id bigint not null,
    primary key (id)
);

Test data:

insert into employees (id, username, password, account_id) values
     (1, "test user", "pass", 3),
     (2, "test user2 ", "pass", 4);

insert into accounts (id, account_name, account_group_id) values
     (1, "main", 3),
     (2, "second", 4);
     (3, "third", 4);

I need to create a query which searches into table employees by account_name. I tried this:

Example when I send search param second I need to get a row result: test user2

SELECT * FROM common.employees e
WHERE e.??????? iLIKE CONCAT('%', :params, '%')

Do you know how I can join the tables?

CodePudding user response：

Join the 2 tables like that (result here)

SELECT e.* FROM 
employees e, accounts a
WHERE 
e.account_id = a.id
and a.account_name = 'second'

CodePudding user response：

You cannot directly parameterize Sql identifier(columns, tables), You can only parameterize values.

Prepared statements can take parameters: values that are substituted into the statement when it is executed. https://www.postgresql.org/docs/current/sql-prepare.html

In your code. WHERE e.??????? cannot be easily parameterized. You need to use plpgsql functions.

prepare test(text,int) as SELECT e.* FROM  employees e
join accounts a on e.account_id = a.id
 WHERE a.account_name iLIKE CONCAT('%', $1, '%')
 and a.account_group_id  =  $2;

If your already have test prepare statement in the active session then DEALLOCATE test;

suppose the account_group_id = 1 then:
execute test('third', 1);

CodePudding user response：

To include columns account_group_id and account_id into the result you can get as below : Though e.* will contain all the info that is present in employee table which include account_id as well. So if you want to customized your result set you can do that according to your need:

SELECT e.*,a.account_group_id 
FROM  employees e
INNER JOIN accounts a ON a.id = e.account_id
WHERE a.account_name = param

CodePudding user response：

If you just use an inner join and join on the account table using the account_id and add a WHERE clause where you only select from employee where the account_name equals your param....which I'm guessing will be a varchar

SELECT e.*, a.account_group_id 
FROM  employees e
INNER JOIN accounts a ON a.id = e.account_id
WHERE a.account_name = param

WHERE a.account_name LIKE '%param%'

but the second may bring back other users as the param could exist in other names.

Also I don't believe the data in your example is correct as surely the account_id would link to the id in the accounts table...so passing second would in fact get you an employee who's account_id is 2.