mysql select query - a tricky one - Databases forum at WebmasterWorld

Hello everyone,

What i have is a number of customers that can belong to a number of groups. Thus, the customers table is joined to a relational customer_group table, so a single customer (a,b,c) can belong to a number of groups (1,2,3). So the customer group table looks like the following:

a бі 1
a бі 2
b бі 2
a бі 3
c бі 1
d бі 2
c бі 3

what i need to achieve is this: GROUP 2 MUST BE RETURNED OVER ANY OTHER GROUP, AND IF A CUSTOMER BELONGS TO A DIFFERENT GROUP BUT NOT GROUP 2, THEN SHOW THAT GROUP NUMBER. So i want this result:

a бі 2
b бі 2
c бі 1 (or 3, doesn't matter if not group 2)
d бі 2

I cannot make a successful query to give me this result. Please Help! Thanks. -bp

mysql> SELECT * FROM customers;
+----------+
і customer і
+----------+
і a і
і b і
і c і
і d і
+----------+

mysql> SELECT * FROM customer_group;
+-------------------------+----------------------+
і customer_group_customer і customer_group_group і
+-------------------------+----------------------+
і a і 1 і
і a і 2 і
і a і 3 і
і b і 1 і
і b і 3 і
і c і 2 і
і c і 3 і
і c і 4 і
+-------------------------+----------------------+

SELECT customer,
IF(MAX(two) = TRUE, 2, customer_group_group) AS customer_group
FROM (
SELECT customers.customer,
customer_group.customer_group_group,
IF(customer_group.customer_group_group = 2, TRUE, FALSE) AS two
FROM customers
INNER JOIN customer_group ON customers.customer = customer_group.customer_group_customer
) tmp
GROUP BY customer;

+----------+----------------+
і customer і customer_group і
+----------+----------------+
і a і 2 і
і b і 1 і
і c і 2 і
+----------+----------------+

mysql select query - a tricky one

select query

bmilesp

Rebrandt

bmilesp

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week