optimizing a query

[fenway]

I don't understand what you mean by precedence. The JOINs will pull them out, sure, but you won't be able to order them that way.

[jworisek]

by precedence I simply mean that it should check bill_low then bill_medium, then bill_high. If there are any rows in bill_high, it should be taken over the other two, then bill_medium, etc.

Each of the tables has a price column so what I wrote works fine if there is a value in the bill_high, but if there is no record in bill_high it returns one row but no price.

Is there a btter way to word a query like that to obtain the results or should I just stick with 3 seperate queries and comparing them afterword?

[fenway]

That might just work -- though I've never tried it. Usually, I would UNION all the bill_ tables together, add a field to mark which the source table, group by bill, and order by precedence. I'm sure someone else has another (probably better) way to do it.

[shoz]

I don't think I've interpreted what you're asking accurately. Perhaps you could post the current queries and any programming logic you're using to get the result you're looking for.

Is this a test for one line item or is this to generate a result for all line items? Do you have a column in the bill tables to specify which line item this bill is for?

I'm sure it's clear from the questions that I don't understand how everything is related or what the query is trying to achieve.

[shoz]

I should mention that you can try this if everything else is fine though.
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] IFNULL(BH.price,IFNULL(BM.price, BL.price)) [color=green]FROM[/color] [color=orange]bill_type[/color] [!--sql2--][/div][!--sql3--]

[jworisek]

after screwing around with it for a bit I did get it to work using the IFNULL suggestion, although I had to change it to say

[code]
SELECT IFNULL(BH.price,IFNULL(BM.price, BL.price)) AS price
[/code]

Or else it wouldn't return any price results.

is there a way to return some string variable as well to let me know which table was selected to be used? That would be really helpful for testing and debugging. If not I can always just select BH.id as BHid, BM.id as BMid, etc and then just use php to check for precedence and print out a string based on that.

[shoz]

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span]
IFNULL(BH.price,IFNULL(BM.price, BL.price)) [color=green]AS[/color] price,
IF (ISNULL(BH.price), IF(ISNULL(BM.price), [color=red]'BL'[/color], [color=red]'BM'[/color]), [color=red]'BH'[/color]) [color=green]AS[/color] tbl [!--sql2--][/div][!--sql3--]
It may be better to use the same method for choosing the price as when choosing the table. Although it shouldn't matter theoretically. I'd still do it.
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span]
IF (ISNULL(BH.price), IF(ISNULL(BM.price), BL.price, BM.price), BH.price) [color=green]AS[/color] price
IF (ISNULL(BH.price), IF(ISNULL(BM.price), [color=red]'BL'[/color], [color=red]'BM'[/color]), [color=red]'BH'[/color]) [color=green]AS[/color] tbl [!--sql2--][/div][!--sql3--]
Concatenating the name to the price is also an option instead of using seperate columns

[jworisek]

Thanks a lot for all the help!

After thinking about it a bit I will probably be pulling the vaules for all the tables as well because it would be pretty useful for debugging if I can post a small note on the report with the table used highlighted and all the other tables listed with prices they have. At least until we get all the bugs worked out.

[shoz]

[quote]
After thinking about it a bit I will probably be pulling the vaules for all the tables as well
[/quote]
Just keep in mind that I don't think I know enough about the tables to consider problems .You'll have to make sure that if there are any errors that you'll find them.

[jworisek]

[!--quoteo(post=350281:date=Feb 28 2006, 11:04 AM:name=shoz)--][div class=\'quotetop\']QUOTE(shoz @ Feb 28 2006, 11:04 AM) [snapback]350281[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Just keep in mind that I don't think I know enough about the tables to consider problems .You'll have to make sure that if there are any errors that you'll find them.
[/quote]

Believe me, there will be more hours of my time put to testing this than I care to think about at the moment ;)