Message Board
| Message Board > Others > Not really a game dev issue but... |
| July 13, 2007, 14:10 | |
|
Dennis
どこかにいる 2097 posts 
|
I hope there are some sql gurus here. I have this query select blah, bleh, bluh from tbl t where t.first in (select one from second table) and t.second in (select two from second table) and t.third in (select threefrom second table); I want to avoid three subselects, and keep only one subselect. How is this possible? I heard joining the subslect as correlated query with the outer select statement with an "exists" clause... but is that really much more performant? And why would it? in my case the table tbl and second_table are gigantic, thus joining would only slow things down... I doubt anyone here can help me (not many of you are into sql programming I think, since it is not that gamedev related; except for php people creating a php game online), but if someone can, I'd appreciate it. ____________ Kwakkel |
| # | |
| July 13, 2007, 18:36 | |
|
PEader
お前はもう死んでいる 1486 posts 
|
Can you state a bit more clearly what you want to do? I use SQL a bit but your jargon is confusing me as to what exactly you are trying out. I mean for Code: t.first in (select one from second table) Do you mean there is only one of t.first in the second table or that there could potentially be millions? ____________ I see 57,005 people. |
| # | |
| July 13, 2007, 18:56 | |
|
Eckolin
Quite Whiskered 388 posts 
|
Why do the subselects have to go?
____________ Maker of Games... Wisdom is supreme; therefore get wisdom. Need help with coding? I probably wrote something similar. |
| # | |
| July 14, 2007, 14:18 | |
|
Dennis
どこかにいる 2097 posts
|
the jargon is oracle. I already fiound it, you can use brackets select col1 from tab where (tab.col1, tab.col2, ...) in (select tab2.col1, tab2.col2, ... from tab tab2) the in operator is like the OR operator select 1 from tab t1 where t1.col1 in (select 1 from tab t2) other ex: select 1 from tab t where t.col1 in (1,2,5,9,1000) = select 1 from tab t where t.col1 = 1 or t.col1 = 2 or t.col1 = 5 or t.col1=9 or t.col1 = 1000. if there are 5 rows in the subselect it will use an OR. if you say 'not in' it uses AND. So there could be millions, but this is quickly written, isntead of a million "or" statements. ____________ Kwakkel |
| # | |
| October 31, 2008, 01:13 | |
|
Dennis
どこかにいる 2097 posts
|
It's more efficient to use exists and do a corelated join into the subselect. You win some performance... sigh... If I chose for HR instead of IT I'd see more women daily... 
____________ Kwakkel |
| # | |
| October 15, 2010, 12:33 | |
|
Dennis
どこかにいる 2097 posts
|
adding 2 years SQL experience: SQL code: SELECT blah, bleh, bluh FROM tbl t JOIN second_table s ON ( t.first = s.one AND t.second = s.two AND t.third = s.three ); Most performant solution [Edited on October 15, 2010 by Dennis] ____________ Kwakkel |
| # | |
Message Board > Others > Not really a game dev issue but...