Page 1 of 1

query not returning any results..what am i missing?

Posted: Fri Jan 23, 2015 4:15 am
by jonnyfortis
i have done this so many times...so what am i missing

i have three tables that all have the correct data inserted.

Code: Select all

SELECT * FROM host_editprop2014, host_payments2014, plus_signup2014 WHERE plus_signup2014.userid = host_payments2014.payment_userid AND host_editprop2014.prop_id = plus_signup2014.prop_id AND plus_signup2014.propFull = host_editprop2014.prop_id

i know the data within the tables is correct but nothing is being returned

Code: Select all

 MySQL returned an empty result set (i.e. zero rows). (Query took 0.0213 seconds.)
i will try and expand
if i remove

Code: Select all

 AND plus_signup2014.propFull = host_editprop2014.prop_id
i get results returned but the data appears to be in the wrong columns

the same applies for

Code: Select all

 AND host_editprop2014.prop_id = plus_signup2014.prop_id
if i remove

Code: Select all

 plus_signup2014.userid = host_payments2014.payment_userid AND
i get no results returned

but if i run this query on its own i get results

am i missing something?
thanks

Re: query not returning any results..what am i missing?

Posted: Fri Jan 23, 2015 4:58 am
by requinix
How about an example of what data you have and what you expect to return?

Re: query not returning any results..what am i missing?

Posted: Fri Jan 23, 2015 5:06 am
by jonnyfortis
requinix wrote:How about an example of what data you have and what you expect to return?
ok here you go

plus_signup2014.userid = bob smith
host_payments2014.payment_userid = bob smith
host_editprop2014.prop_id = 40 NAP-1
plus_signup2014.prop_id = 40 NAP-1
plus_signup2014.propFull = 40 NAP
host_editprop2014.prop_id = 40 NAP

so to return i want to see

username | prop ID | full prop
bob smith |40 NAP-1 | 40 NAP

Re: query not returning any results..what am i missing?

Posted: Fri Jan 23, 2015 5:16 am
by Celauran

Code: Select all

SELECT *
FROM host_editprop2014, host_payments2014, plus_signup2014
WHERE plus_signup2014.userid = host_payments2014.payment_userid
AND host_editprop2014.prop_id = plus_signup2014.prop_id
AND plus_signup2014.propFull = host_editprop2014.prop_id
According to the last two WHERE clauses, plus_signup2014.prop_id and plus_signup2014.propFull must have the same value since they're both equal to host_editprop2014.prop_id

Re: query not returning any results..what am i missing?

Posted: Fri Jan 23, 2015 5:49 am
by jonnyfortis
Celauran wrote:

Code: Select all

SELECT *
FROM host_editprop2014, host_payments2014, plus_signup2014
WHERE plus_signup2014.userid = host_payments2014.payment_userid
AND host_editprop2014.prop_id = plus_signup2014.prop_id
AND plus_signup2014.propFull = host_editprop2014.prop_id
According to the last two WHERE clauses, plus_signup2014.prop_id and plus_signup2014.propFull must have the same value since they're both equal to host_editprop2014.prop_id
yes, within the colunm host_editprop2014.prop_id we are storing both 40 NAP-1 and 40 NAP

Re: query not returning any results..what am i missing?

Posted: Fri Jan 23, 2015 7:28 am
by Celauran
jonnyfortis wrote:yes, within the colunm host_editprop2014.prop_id we are storing both 40 NAP-1 and 40 NAP
I'm not clear on what you mean. How are you storing two values in the same column? Surely, that can't be right.

Re: query not returning any results..what am i missing?

Posted: Fri Jan 23, 2015 7:42 am
by jonnyfortis
Celauran wrote:
jonnyfortis wrote:yes, within the colunm host_editprop2014.prop_id we are storing both 40 NAP-1 and 40 NAP
I'm not clear on what you mean. How are you storing two values in the same column? Surely, that can't be right.
i have made another table and created anther join for propFull so now it works.
thanks for pointing the floor out in my query, it cant reference two different searches using the same thing...if that makes sense.