Welcome to all phpBB2 Refugees!This site is intended to continue support for the legacy 2.x line of the phpBB2 bulletin board package. If you are a fan of phpBB2, please, by all means register, post, and help us out by offering your suggestions. We are primarily a community and support network. Our secondary goal is to provide a phpBB2 MOD Author and Styles area. |
|
Author |
Message |
JLA Board Member
Joined: 30 Apr 2009
Posts: 451 Location: U.S.A
|
Posted: Thu Jun 20, 2013 4:42 pm Post subject: Big Problem query - join or what? |
|
|
Have a query running that returns a rather large result that we're trying to trim down.
Basically its on one table
Example table TEST
Field1 Field2 Field3
testval1 testval2 0
testval3 testval4 1
Standard query is
Select s.field1, s.field2, s.field3
from TEST s
order by s.field2
Returns
Row 1: Field1 = testval1, Field2= testval2, Field3= 0
Row 2: Field1= testval3, Field2= testval4, Field3= 1
Problem is that when this query is ran, (many many rows in table and Field 2 values are large in size) there is a rather large result received. Later on in the code, the rows that have Field3= 1 do not make use (or need) of the Field2 value. So was looking at changing the query to something like this?
Select s.field1, s.field3, sd.field1, sd.field2, sd. field3
from TEST S
LEFT JOIN TEST sd
ON s.field1 = sd.field1
AND sd.field3 < 1
order by s.field2
Returns
Row 1: Field1 = testval1, Field3= 0 Field1= testval1, Field2= testval3, Field3= 0
Row 2: Field1= testval3, Field3= 1 Field1= null, Field2= null, Field3= Null
Problem is afterwards the sd.field 1, 2 and 3 being null in this join is cancelling out the s.field 1 and 3 for the rows where sd.field3 =1
What we need to be able to have is
Returns
Row 1: Field1 = testval1, Field3= 0 Field1= testval1, Field2= testval3, Field3= 0
Row 2: Field1= testval3, Field3= 1 Field1= null, Field2= null, Field3= Null
If Field 3 !=1
$val1 = testval1
$val2 = testval2
$val3 = 0
If Field3 ==1
$val1 = testval3
$val2 = NULL
$val3 = 1
Chances are something simple being missed here... Maybe the join is not correct? If so then the Returns portion would be different. We just need the if Field 3= portions to come out as stated. Any fresh thoughts are appreciated. _________________ http://www.jlaforums.com |
|
Back to top |
|
|
Salvatos Board Member
Joined: 19 Feb 2009
Posts: 449 Location: Québec
|
Posted: Thu Jun 20, 2013 10:47 pm Post subject: Re: Big Problem query - join or what? |
|
|
Admittedly I just skimmed over your post so I may be overlooking something, but couldn't you run two queries and join the results application-side?
One query SELECT * WHERE field3 = 0.
One query SELECT field1, field3 WHERE field3 = 1.
I don't know anything about optimization, so that might take even longer than gettting everything at once for all I know, but I thought I'd state the obvious in case you overlooked it. |
|
Back to top |
|
|
JLA Board Member
Joined: 30 Apr 2009
Posts: 451 Location: U.S.A
|
Posted: Fri Jun 21, 2013 6:56 am Post subject: Re: Big Problem query - join or what? |
|
|
Salvatos wrote: | Admittedly I just skimmed over your post so I may be overlooking something, but couldn't you run two queries and join the results application-side?
One query SELECT * WHERE field3 = 0.
One query SELECT field1, field3 WHERE field3 = 1.
I don't know anything about optimization, so that might take even longer than gettting everything at once for all I know, but I thought I'd state the obvious in case you overlooked it. |
So perhaps using Union??? _________________ http://www.jlaforums.com |
|
Back to top |
|
|
JLA Board Member
Joined: 30 Apr 2009
Posts: 451 Location: U.S.A
|
Posted: Fri Jun 21, 2013 6:04 pm Post subject: Re: Big Problem query - join or what? |
|
|
Ah, what a difference a bit of REM makes (also corrects some missteps above)
Example table TEST
Field1 Field2 Field3
testval1 testval2 0
testval3 testval4 1
Select sd.field1, sd. field3, s.field1, s.field2, s.field3,
from TEST sd
RIGHT JOIN TEST s
ON sd.field1 = s.field1
AND sd.field3 < 1
order by s.field2
Returns
Row 1: Field1 = testval1, Field3 =0, Field1=testval1, Field2=testval2, Field3 = 0
Row 2: Field1 = null, Field3 = Null, Field1=testval3, Field2=testval4, Field3=1 _________________ http://www.jlaforums.com |
|
Back to top |
|
|
|
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0537 seconds using 16 queries. (SQL 0.0087 Parse 0.0008 Other 0.0442) |
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com
|
|