How to search from a table using join?

So I have three tables as follows:

Members -> id, userId, accountId

Account -> id, name

User -> id, firstName, lastName, email

INSERT INTO `account` (`id`, `name`) VALUES
('1', 'xyz company'),
('2', 'abc company');

INSERT INTO `users` (`id`, `email`, `firstName`, `lastName`) VALUES
('1', 'abc@gmail.com', 'earth', 'flat'),
('2', '1@gmail.com', 'One hundred', 'pin'),
('3', '2@gmail.com', 'bull', 'horn'),
('4', '3@gmail.com', 'ball', 'yellow');

INSERT INTO `members` (`id`, `userId`, `accountId`) VALUES
('1', '1', '1'),
('2', '1', '2'),
('3', '2', '1');

SELECT
  m.id,
  m.accountId,
  (
    SELECT
      JSON_BUILD_OBJECT(
        'userId', u.id, 
        'firstName', u.firstName, 
        'lastName', u.lastName, 
        'email', u.email
      )
    FROM user
    WHERE user.id = m.userId
  ) AS user
FROM users AS u
INNER JOIN members m ON m.userId = u.id
WHERE am.accountId = 1
GROUP BY m.id, u.id

The above sql gives me correct number of data as follows.

('accountId', 'email', 'firstName', 'lastName')
('1', 'abc@gmail.com', 'earth', 'flat')
('1', '1@gmail.com', 'One hundred', 'pin')

But when I do a search on the users from the same table, it gives me data also from accountId other than 1 when it should only give data from accountId 1.

SELECT
  m.id,
  m.accountId,
  (
    SELECT
      JSON_BUILD_OBJECT(
        'userId', u.id, 
        'firstName', u.firstName, 
        'lastName', u.lastName, 
        'email', u.email
      )
    FROM user
    WHERE user.id = m.userId
  ) AS user
FROM users AS u
INNER JOIN members m ON m.userId = u.id
WHERE am.accountId = 1,
OR LOWER(u.first_name) LIKE LOWER('%gma%')
OR LOWER(u.last_name) LIKE LOWER('%gma%')
OR LOWER(u.email) LIKE LOWER('%gma%')
GROUP BY m.id, u.id

This gives me all four data:

('accountId', 'email', 'firstName', 'lastName')
('1', 'abc@gmail.com', 'earth', 'flat'),
('2', 'abc@gmail.com', 'earth', 'flat'),
('1', '1@gmail.com', 'One hundred', 'pin')

The result should display only from accountID 1 but since there is matching in the name, its giving more result than expected.

The above query matches the LIKE with the user and returns members other than from accountId 1 which is true, but how can I write the query so that members are listed from accountId and also can be searched via name, email in users table.

Answer

Just keep in simple, something like this perhaps.

 SELECT 
  m.id, m.accountId,
  u.id, u.firstName, u.lastName,
  json_build_object('userId', u.id, 'firstName', u.firstName, 'lastName', u.lastName, 'email', u.email) as userobject,
  a.id as account_id,
  a.name as accountname


FROM
    users AS u
    LEFT JOIN members m ON m.userId = u.id  -- user and member

    LEFT JOIN  account as a  ON  a.id = m.accountId   -- connect to account from member

WHERE 
    (   
      u.firstName like '%gma%'
      0R 
        u.lastName  LIKE '%gma%'
       OR 
       u.email  LIKE '%gma%'
    )
 and   am.accountId = 1

Advertisement

Answer