I have 2 tables.
Table transaction:
id | customer_id | department_id
--------------------------------
1 | 1 | 2
--------------------------------
2 | 2 | 3
--------------------------------
3 | 2 | 4
--------------------------------
4 | 3 | 1
--------------------------------
5 | 2 | 3
--------------------------------
Table des_department
id | caption
-----------------
1 | department1
-----------------
2 | department2
-----------------
3 | department3
-----------------
4 | department4
-----------------
5 | department5
-----------------
I need to display department caption for each customer_id. Choose department that user visits more than others.
Example of ouput:
customer_id | caption
------------------------------
1 | department2
------------------------------
2 | department3
------------------------------
3 | department1
------------------------------
I have also own query. But I display number of all visits.
My query:
SELECT t.customer_id, t.terminal_i
FROM transaction t WHERE (t.customer_id, t.terminal_id) IN
( SELECT t1.customer_id, t1.terminal_id
FROM transaction t1 GROUP BY t1.customer_id
)
I work on dbForge for Mysql.
I write this query by researching in the internet, and may be it will be helpfull for someone. Here is sql code:
SELECT customer_id, dd.caption
FROM
(SELECT t.customer_id, t.deparment_id
FROM (SELECT t.customer_id, t.deparment_id, COUNT(t.deparment_id) AS ter
FROM transaction t
GROUP BY t.customer_id, t.deparment_id) t
WHERE NOT EXISTS (SELECT 1
FROM (SELECT t.customer_id, t.deparment_id, COUNT(t.deparment_id) AS ter
FROM transaction t
GROUP BY t.customer_id, t.deparment_id) td
WHERE td.customer_id = t.customer_id AND td.ter>t.ter
))
tx
INNER JOIN des_department dd
ON tx.deparment_id = dd.id