phpBB2Refugees.com Logo
Not affiliated with or endorsed by the phpBB Group

Register •  Login 

Continue the legacy...

Welcome to all phpBB2 Refugees!Wave Smilie

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.

Big Problem query - join or what?


 
Search this topic... | Search phpBB2 Discussion... | Search Box
Register or Login to Post    Index » phpBB2 Discussion  Previous TopicPrint TopicNext Topic
Author Message
JLA
Board Member



Joined: 30 Apr 2009

Posts: 310
Location: U.S.A


flag
PostPosted: Thu Jun 20, 2013 8: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: 425
Location: Qu├ębec


flag
PostPosted: Fri Jun 21, 2013 2:47 am 
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: 310
Location: U.S.A


flag
PostPosted: Fri Jun 21, 2013 10: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: 310
Location: U.S.A


flag
PostPosted: Fri Jun 21, 2013 10: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
Display posts from previous:   
Register or Login to Post    Index » phpBB2 Discussion  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT
 
Jump to:  

Index • About • FAQ • Rules • Privacy • Search •  Register •  Login 
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0285 seconds using 15 queries. (SQL 0.0045 Parse 0.0009 Other 0.0231)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo