Jump to content

Archived

This topic is now archived and is closed to further replies.

jworisek

optimizing a query

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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--]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[!--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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
[!--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 ;)

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.