CODEWITHSUNDEEP
mysql
user633551
user633551

Reputation: 163

In need of a query logic, how to group by id from the users table?

I have two tables:

INSERT INTO `companies` (`name`) VALUES
('Walmart'),
('Disney'),
('Amazon'),
('Unicom'),
('Microsoft'),
('Intel')

INSERT INTO `users` (`id`, `company`) VALUES
(1, 'Disney'),
(2, 'Amazon'),
(3, 'Intel'),
(3, 'Walmart'),
(4, 'Microsoft'),
(4, 'Unicom'),
(5, 'Microsoft')

The result should be following:

1. 'Walmart', 'Amazon', 'Unicom', 'Microsoft', 'Intel'
2. 'Walmart', 'Disney', 'Unicom', 'Microsoft', 'Intel'
3. 'Disney', 'Amazon', 'Unicom', 'Microsoft'
4. 'Walmart', 'Disney', 'Amazon', 'Intel'
5. 'Walmart', 'Disney', 'Amazon', 'Unicom', 'Intel'

I have tried with:

"SELECT a.name, b.id, b.company FROM users RIGHT JOIN companies ON b.company <> a.name"

This gives the correct logic by omitting the company name that's already on the list but the problem is that it processes the same id twice and omits a different company name. How would one approach this query?

Upvotes: 1

Views: 38

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

The basic idea in the query below is to left join a calendar table containing every possible user/company combination to the users table. Those combinations which do match are removed, and the remaining companies are then rolled up into a CSV string for each user using GROUP_CONCAT.

SELECT t1.id, GROUP_CONCAT(t1.name)
FROM
(
    SELECT DISTINCT u.id, c.name
    FROM users u
    CROSS JOIN companies c
) t1
LEFT JOIN users t2
    ON t1.name = t2.company AND t1.id = t2.id
WHERE t2.company IS NULL
GROUP BY
    t1.id;

enter image description here

Demo

Upvotes: 2

Pavan Sikarwar
Pavan Sikarwar

Reputation: 798

Try this 

"SELECT a.name, b.id, b.company FROM users RIGHT JOIN companies ON b.company <> a.name group by b.company"

Upvotes: 0

Related Questions