Jump to content

optimizing a query


jworisek

Recommended Posts

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?
Link to comment
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.
Link to comment
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.
Link to comment
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--]
Link to comment
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.
Link to comment
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
Link to comment
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.
Link to comment
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.
Link to comment
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 ;)
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.