[Pro] Combining 2 MySQL queries into one

Legacy Dynamo
1

Hi All,

I Have a MySQL database that holds information on products, description prices etc, and I need to select 2 separate product groups from 1 column and display the selected products on the page.

I know you can query the database twice, but I wondered if it would be better to just query it once, the two separate queries are:


SELECT cesproducts.*, cesprices.price FROM cesproducts, cesprices
WHERE `cesproducts`.`prodline` = 'JCS Hose and P Clips'
AND `cesproducts`.`partnumber` = `cesprices`.`partnumber`

and

SELECT cesproducts.*, cesprices.price FROM cesproducts, cesprices
WHERE `cesproducts`.`prodline` = 'Mikalor Clips and Clamps'
AND `cesproducts`.`partnumber` = `cesprices`.`partnumber`

The data will be displayed using scripty accordian to reveal the selected product range.

Any solutions gratefully received.

Steve

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

2

I would go with the two queries rather than trying to optimize in this
manner.

You could use this WHERE clause: WHERE prodline IN (JCS Hose and P Clips','Mikalor Clips and Clamps') to get them both together, but
then you have the overhead of sorting the results, as well as if you
want to have a different header over each group, you will need to
inspect each item in your results set to see if it has a different
prodline than the item before it…

If you’re showing an undifferentiated list of items that could be from
either one prodline or the other, and you don’t care about the sort or
grouping, then you could use the IN() trick to get either.

Walter

On Sep 20, 2010, at 12:49 PM, SteveB wrote:

Hi All,

I Have a MySQL database that holds information on products,
description prices etc, and I need to select 2 separate product
groups from 1 column and display the selected products on the page.

I know you can query the database twice, but I wondered if it would
be better to just query it once, the two separate queries are:


SELECT cesproducts.*, cesprices.price FROM cesproducts, cesprices
WHERE `cesproducts`.`prodline` = 'JCS Hose and P Clips'
AND `cesproducts`.`partnumber` = `cesprices`.`partnumber`

and

SELECT cesproducts.*, cesprices.price FROM cesproducts, cesprices
WHERE `cesproducts`.`prodline` = 'Mikalor Clips and Clamps'
AND `cesproducts`.`partnumber` = `cesprices`.`partnumber`

The data will be displayed using scripty accordian to reveal the
selected product range.

Any solutions gratefully received.

Steve

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

3

Hi Walter,

Thanks for the reply, I thought that the 2 query route was the easiest, but I’ll definitely look at using the IN() to see how the results come.

It seems that there are always more than on way to go to get the result.

Thanks again,

Steve.

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options