Select from Two Tables

[Frank_Rizzo]

Two tables:

cellphones
salesperson
sales

e.g.
A Harrison, 25
B Bryan, 18
H Potter, 85

cars
salesperson
sales

e.g.
A Harrison, 10
H Potter, 120
L Ford, 32

I'm trying to create a queery which will produce the ratio of cellphone sales to car sales. The ratio formula = cellphone sales / car sales

e.g.

A Harrison 2.5 (25/10)
H Potter 0.708 (85/120)

From that I can deduce that A Harrison sells 2.5 times more cellphones than cars, whilst H Potter sells only 0.708 times as many cellphones than cars.

I currently do this by running two seperate selects in a php loop

select sales from cellphones where salesperson='A Harrison'
select sales from cars where salesperson='A Harrison'
php code to check divide both results and loop through to next name

Is there a way to do this with just one select? And possibly without having to loop / while the data in php?

Note that there is a gotcha in that some salespersons are not in both tables so could produce nn / 0 inifinity error such as B Bryan and L Ford.

[edited by: Frank_Rizzo at 8:22 pm (utc) on Nov. 21, 2007]

[syber]

This should give you what you need:

SELECT cellphones.salesperson, cellphones.sales / cars.sales AS ratio
FROM cellphones JOIN cars ON cellphones.salesperson = cars.salesperson

(The way a JOIN works will take care of the situation where a salesperson in not in both tables - they will not show)

[Frank_Rizzo]

That works a treat.

Thanks.

[ZydoSEO]

Be careful of situations where a salesperson has sold zero (0) cars. You need to handle this situation to avoid DIVIDE BY ZERO errors.

[syber]

According to the example, a salesperson will only have an entry in the cars table if he has sold at least one car.

Therefore, you cannot have a divide by zero. The JOIN will eliminate any row that doesn't have a matching entry in the other table.