fenway Posted February 27, 2006 Share Posted February 27, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/3516-optimizing-a-query/page/2/#findComment-12885 Share on other sites More sharing options...
jworisek Posted February 27, 2006 Author Share Posted February 27, 2006 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? Quote Link to comment https://forums.phpfreaks.com/topic/3516-optimizing-a-query/page/2/#findComment-12888 Share on other sites More sharing options...
fenway Posted February 28, 2006 Share Posted February 28, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/3516-optimizing-a-query/page/2/#findComment-12931 Share on other sites More sharing options...
shoz Posted February 28, 2006 Share Posted February 28, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/3516-optimizing-a-query/page/2/#findComment-12958 Share on other sites More sharing options...
shoz Posted February 28, 2006 Share Posted February 28, 2006 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--] Quote Link to comment https://forums.phpfreaks.com/topic/3516-optimizing-a-query/page/2/#findComment-12980 Share on other sites More sharing options...
jworisek Posted February 28, 2006 Author Share Posted February 28, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/3516-optimizing-a-query/page/2/#findComment-13026 Share on other sites More sharing options...
shoz Posted February 28, 2006 Share Posted February 28, 2006 [!--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] priceIF (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 Quote Link to comment https://forums.phpfreaks.com/topic/3516-optimizing-a-query/page/2/#findComment-13029 Share on other sites More sharing options...
jworisek Posted February 28, 2006 Author Share Posted February 28, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/3516-optimizing-a-query/page/2/#findComment-13030 Share on other sites More sharing options...
shoz Posted February 28, 2006 Share Posted February 28, 2006 [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. Quote Link to comment https://forums.phpfreaks.com/topic/3516-optimizing-a-query/page/2/#findComment-13053 Share on other sites More sharing options...
jworisek Posted February 28, 2006 Author Share Posted February 28, 2006 [!--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 ;) Quote Link to comment https://forums.phpfreaks.com/topic/3516-optimizing-a-query/page/2/#findComment-13073 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.