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 TorontoSoftware DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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
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
APD TorontoSoftware DeveloperAuthor Commented:
chaau,

This gives me 99% of what I need, but in the result can I get "family1", "adult1", "child1", "family2", "adult2", "child2",
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

APD TorontoSoftware DeveloperAuthor Commented:
..as column headings?
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.
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

APD TorontoSoftware DeveloperAuthor 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

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

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

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 TorontoSoftware DeveloperAuthor Commented:
Thanks
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.