mySQL query not fuctioning correctly

[Ben Allen]

Hi,
I'm using a piece of software called 'phpcoin' along with the popular
'IPN' mod. I am having some problem with it and have got it down to an
mySQL function functioning incorrectly.

This is the structure of the tables involved:

phpcoin_clients:
Field Type Null Default
cl_id int(11) No 0
cl_join_ts varchar(10) Yes NULL
cl_status varchar(20) No pending
cl_company varchar(50) No
cl_name_first varchar(20) No
cl_name_last varchar(20) No
cl_addr_01 varchar(50) No
cl_addr_02 varchar(50) No
cl_city varchar(50) No
cl_state_prov varchar(50) No
cl_country varchar(50) No
cl_zip_code varchar(12) No
cl_phone varchar(20) No
cl_email varchar(50) No
cl_user_name varchar(20) No
cl_user_pword varchar(50) No
cl_notes text No
cl_groups int(11) No 0

phpcoin_clients_contacts:
Field Type Null Default
contacts_id int(11) No
contacts_cl_id int(11) No 0
contacts_name_first varchar(20) No
contacts_name_last varchar(20) No
contacts_email varchar(50) No

The sql query is as follows:

SELECT cl_id from phpcoin_clients, phpcoin_clients_contacts
WHERE (cl*************@domain.com')
OR (co*******************@domain.com')
AND contacts_cl_id=cl_id

This only returns the client id (cl_id) when the contacts_email is set
to em***@domain.com.

If the query is just:
SELECT cl_id from phpcoin_clients
WHERE (cl*************@domain.com')

Then the result is as expected, anyone have any ideas as to why this is?

Thanks,
Ben

[Bill Karwin]

Ben Allen wrote:

SELECT cl_id from phpcoin_clients, phpcoin_clients_contacts
WHERE (cl*************@domain.com')
OR (co*******************@domain.com')
AND contacts_cl_id=cl_id

This only returns the client id (cl_id) when the contacts_email is set
to em***@domain.com.

Yes, this is working correctly, because AND binds tighter than OR.

(X OR Y AND Z) is the same as (X OR (Y AND Z)).
You need to use parentheses to override that behavior and get
((X OR Y) AND Z).

So you could rewrite the query as follows:

SELECT cl_id from phpcoin_clients, phpcoin_clients_contacts
WHERE (cl*************@domain.com' OR co*******************@domain.com')
AND contacts_cl_id=cl_id

Regards,
Bill K.

[Ben Allen]

Bill Karwin wrote:

Ben Allen wrote:

SELECT cl_id from phpcoin_clients, phpcoin_clients_contacts
WHERE (cl*************@domain.com')
OR (co*******************@domain.com')
AND contacts_cl_id=cl_id

This only returns the client id (cl_id) when the contacts_email is set
to em***@domain.com.

Yes, this is working correctly, because AND binds tighter than OR.

(X OR Y AND Z) is the same as (X OR (Y AND Z)).
You need to use parentheses to override that behavior and get
((X OR Y) AND Z).

So you could rewrite the query as follows:

SELECT cl_id from phpcoin_clients, phpcoin_clients_contacts
WHERE (cl*************@domain.com' OR co*******************@domain.com')
AND contacts_cl_id=cl_id

Regards,
Bill K.

Thanks that's working better, however, now if there is no cl_id in the
contacts table (contacts_cl_id), then nothing is returned. is this
because the line

SELECT cl_id from phpcoin_clients, phpcoin_clients_contacts

is being called even though there is no cl_id in
phpcoin_clients_contacts just contacts_cl_id?

Thanks Again,
Ben

[Bill Karwin]

Ben Allen wrote:

now if there is no cl_id in the
contacts table (contacts_cl_id), then nothing is returned.

This is because your query is of the type that is termed an "inner
join". Records are part of the query's result set only when
contacts_cl_id = cl_id. This means that there _must_ be a matching
value in both tables for that condition to be satisfied.

You probably need an "outer join." This is the type of join that
returns all records from one table, even if no matching records exist in
the other table. If you were selecting fields from the second table in
the select-list, they'd show up as NULL if there were no record matching
the first table.

SELECT cl.cl_id
FROM phpcoin_clients AS cl LEFT OUTER JOIN
phpcoin_clients_contacts AS co
ON cl.cl_id = co.contacts_cl_id
WHERE cl****************@domain.com'
OR co**********************@domain.com'

Regards,
Bill K.