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.
APD TorontoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
APD_Toronto,

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
0
chaauCommented:
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

Open in new window

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 |

Open in new window

SQL Fiddle
0
APD TorontoAuthor Commented:
chaau,

This gives me 99% of what I need, but in the result can I get "family1", "adult1", "child1", "family2", "adult2", "child2",
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

APD TorontoAuthor Commented:
..as column headings?
0
chaauCommented:
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.
0
chaauCommented:
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

Open in new window

0
APD TorontoAuthor Commented:
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

Open in new window

0
chaauCommented:
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
0
APD TorontoAuthor Commented:
But it works without that.  Am I missing something? Why not just the left portion?
0
chaauCommented:
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

Open in new window

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
0
chaauCommented:
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)
0
APD TorontoAuthor Commented:
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:

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;

Open in new window

0
chaauCommented:
In this case your simpler version of the query will work just fine
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
APD TorontoAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.