Link to home
Start Free TrialLog in
Avatar of APD Toronto
APD TorontoFlag for Canada

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.
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

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
Avatar of chaau
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
Avatar of APD Toronto

ASKER

chaau,

This gives me 99% of what I need, but in the result can I get "family1", "adult1", "child1", "family2", "adult2", "child2",
..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

Open in new window

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

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

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

ASKER CERTIFIED SOLUTION
Avatar of chaau
chaau
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks