APD Toronto
asked on
Selecting 2 rows as 1
Hi Experts,
My application has sales agents with different commission rates for park admission. This is represented by the following 2 MySQL tables, where the table names are bolded...
agents
id
name
rates
agent_id
product
family
adult
child
In the rates, each product is a string preceded by 1 and 2... So, possible products values are ABC1, ABC2, DEF1, DEF2, XYZ1, XYZ2
My question now is, how can I write a query, where I can supply the string of the product, Like 'ABC' and I would a single per agent row with the following columns:
agent_id
name
family1
adult1
child1
family2
adult2
child2
Where, family1 is the family rate for ABC1, and adult2 is the adult rate for ABC2?
Thank you.
My application has sales agents with different commission rates for park admission. This is represented by the following 2 MySQL tables, where the table names are bolded...
agents
id
name
rates
agent_id
product
family
adult
child
In the rates, each product is a string preceded by 1 and 2... So, possible products values are ABC1, ABC2, DEF1, DEF2, XYZ1, XYZ2
My question now is, how can I write a query, where I can supply the string of the product, Like 'ABC' and I would a single per agent row with the following columns:
agent_id
name
family1
adult1
child1
family2
adult2
child2
Where, family1 is the family rate for ABC1, and adult2 is the adult rate for ABC2?
Thank you.
You need to join the rates table twice, like this:
select r1.agent_id
,a.name
,r1.family as family1
,r1.adult as adult1
,r1.child as child1
,r2.family as family2
,r2.adult as adult2
,r2.child as child2
FROM agents a INNER JOIN rates r1
ON a.id = r1.agent_id AND r1.product LIKE '%1'
INNER JOIN rates r2
ON a.id = r2.agent_id AND r2.product LIKE '%2' AND CONCAT(LEFT(r2.product, LENGTH(r2.product) - 1), '1') = r1.product
ORDER BY r1.agent_id, r1.product
Results:| agent_id | name | family | adult | child | family | adult | child |
|----------|-------|--------|-------|--------|--------|--------|----------|
| 1 | Peter | Smith | Mary | John | Smith | Bob | Courtney |
| 2 | John | Jones | Ross | Andrew | Jones | Isabel | Maria |
SQL Fiddle
ASKER
chaau,
This gives me 99% of what I need, but in the result can I get "family1", "adult1", "child1", "family2", "adult2", "child2",
This gives me 99% of what I need, but in the result can I get "family1", "adult1", "child1", "family2", "adult2", "child2",
ASKER
..as column headings?
You will get it in the normal MySQL query analyser (whatever it is called). SQLFiddle unfortunatelly has a mind of its own and messes the headings.
Just wanted to add to my previous comment. The caode below is assigning the correct headings:
,r1.family as family1
,r1.adult as adult1
,r1.child as child1
,r2.family as family2
,r2.adult as adult2
,r2.child as child2
ASKER
It works, but why do you have the following on the second-last line?
AND CONCAT(LEFT(r2.product, LENGTH(r2.product) - 1), '1') = r1.product
What I am doing is this. I filter the r1 table to the products ending with '1' (ABC1, CDEF1, MN1, etc) and filter the r2 table for the '2' products (ABC2, CDEF2, MN2, etc). To marry ABC1 with ABC2 I need to convert the ABC2 to ABC1. The statement CONCAT(LEFT(r2.product, LENGTH(r2.product) - 1), '1') replaces the last character of the product from '2' to '1' so it can be joined with the r1.product
ASKER
But it works without that. Am I missing something? Why not just the left portion?
What is the primary key of your rates table? Can an agent have multiple products? Try this statement (it includes the product code) with and without the CONCAT trick:
select r1.agent_id
,a.name
,r1.product as product1
,r2product as product2
,r1.family as family1
,r1.adult as adult1
,r1.child as child1
,r2.family as family2
,r2.adult as adult2
,r2.child as child2
FROM agents a INNER JOIN rates r1
ON a.id = r1.agent_id AND r1.product LIKE '%1'
INNER JOIN rates r2
ON a.id = r2.agent_id AND r2.product LIKE '%2' AND CONCAT(LEFT(r2.product, LENGTH(r2.product) - 1), '1') = r1.product
ORDER BY r1.agent_id, r1.product
I am pretty sure that if you have an agent with ABC and CDE products you will see that without the CONCAT trick the ABC1 will marry with CDE2 and CDE1 will marry with ABC2 in addition to the correct joins
have a look at this SQL Fiddle. It proves my point I have stated in the previous post (note: there are two SQL statements and two results there)
ASKER
chaau,
I adjusted the data in SQLfiddle to provide little more coordinated data, and added a third SQL SELECT statement.
I will always need to retrieve only #1 and #2 products, and I will always know the string. With that said, do you forsee any issues with the following:
I adjusted the data in SQLfiddle to provide little more coordinated data, and added a third SQL SELECT statement.
I will always need to retrieve only #1 and #2 products, and I will always know the string. With that said, do you forsee any issues with the following:
select r1.agent_id
,a.name
,r1.family as family1
,r1.adult as adult1
,r1.child as child1
,r2.family as family2
,r2.adult as adult2
,r2.child as child2
FROM agents a INNER JOIN rates r1
ON a.id = r1.agent_id AND r1.product = 'ABC1'
INNER JOIN rates r2
ON a.id = r2.agent_id AND r2.product = 'ABC2'
ORDER BY r1.agent_id, r1.product;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
I am sure it is very clear to you what you are talking about. But your description is not very helpful. My request is to post before and after tables so we can build a temp temple to understand the problem and test the solution before we post it back.
Thanks,
Mike