Welcome to WebmasterWorld Guest from 34.204.189.171

Forum Moderators: open

Message Too Old, No Replies

mysql select query - a tricky one

select query

     
10:41 pm on Jun 19, 2007 (gmt 0)

New User

10+ Year Member

joined:June 19, 2007
posts:2
votes: 0


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

2:30 pm on June 21, 2007 (gmt 0)

New User

10+ Year Member

joined:Feb 2, 2005
posts:34
votes: 0



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
+----------+----------------+

5:30 pm on June 26, 2007 (gmt 0)

New User

10+ Year Member

joined:June 19, 2007
posts:2
votes: 0


Absolutely Brilliant! Thank you!