FN-FORUM: Sussed it, was: MySQL Query Problems

date posted 31st January 2006 19:46

On Tuesday 31 Jan 2006 4:42 pm, DF Office Mail wrote:
> Just a quick correction to my earlier post, if anyone is following.



> The actual query needs an explicit OR... NULL test as follows:
>
> SELECT ABSClients.*
> FROM `ABSClients`
> LEFT JOIN `ABSdocs_clients`
> ON ABSClients.clientID =3D ABSdocs_clients.clientID
> WHERE ABSdocs_clients.imageID is NULL
> OR ABSdocs_clients.imageID !=3D YOUR SEARCH CONIDION HERE

Have you tested it?

This, I believe, is the correct form of the query:

SELECT ABSClients.*
FROM `ABSClients`
LEFT JOIN `ABSdocs_clients`
ON ABSClients.clientID =3D ABSdocs_clients.clientID
AND ABSdocs_clients.imageID =3D $imageID
WHERE ABSdocs_clients.imageID is NULL

And if anyone's still interested - here's how the query builds up:


mysql> select * from clients;
+----+-------+
| id | name |
+----+-------+
| 1 | john |
| 2 | fred |
| 3 | bill |
| 4 | alice |
| 5 | henry |
+----+-------+
5 rows in set (0.00 sec)

mysql> select * from clientdocs;
+-----+-----+
| cID | dID |
+-----+-----+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 4 | 1 |
+-----+-----+
4 rows in set (0.00 sec)

mysql> select * from clients left join clientdocs as cd on cd.cID =3D=20
clients.id;
+----+-------+------+------+
| id | name | cID | dID |
+----+-------+------+------+
| 1 | john | 1 | 1 |
| 1 | john | 1 | 2 |
| 2 | fred | 2 | 1 |
| 3 | bill | NULL | NULL |
| 4 | alice | 4 | 1 |
| 5 | henry | NULL | NULL |
+----+-------+------+------+
6 rows in set (0.00 sec)

mysql> select * from clients left join clientdocs as cd on cd.cID =3D cli=
ents.id=20
where cd.dID is 1;
+----+-------+------+------+
| id | name | cID | dID |
+----+-------+------+------+
| 1 | john | 1 | 1 |
| 2 | fred | 2 | 1 |
| 4 | alice | 4 | 1 |
+----+-------+------+------+
3 rows in set (0.00 sec)

mysql> select * from clients left join clientdocs as cd on cd.cID =3D cli=
ents.id=20
and cd.dID =3D 1;
+----+-------+------+------+
| id | name | cID | dID |
+----+-------+------+------+
| 1 | john | 1 | 2 |
| 2 | fred | NULL | NULL |
| 3 | bill | NULL | NULL |
| 4 | alice | NULL | NULL |
| 5 | henry | NULL | NULL |
+----+-------+------+------+
5 rows in set (0.00 sec)

mysql> select * from clients left join clientdocs as cd on cd.cID =3D cli=
ents.id=20
and cd.dID =3D 1 where cd.dID =3D NULL;=20
+----+-------+------+------+
| id | name | cID | dID |
+----+-------+------+------+
| 2 | fred | NULL | NULL |
| 3 | bill | NULL | NULL |
| 4 | alice | NULL | NULL |
| 5 | henry | NULL | NULL |
+----+-------+------+------+
4 rows in set (0.00 sec)

mysql> select * from clients left join clientdocs as cd on cd.cID =3D cli=
ents.id=20
and cd.dID =3D 2 where cd.dID is NULL;=20
+----+-------+------+------+
| id | name | cID | dID |
+----+-------+------+------+
| 3 | bill | NULL | NULL |
| 5 | henry | NULL | NULL |
+----+-------+------+------+
2 rows in set (0.00 sec)

--=20
5: When responding to a post, delete all information that is not
strictly necessary. This includes the freelancers tag line and .sig
files. Delete anything you're not specifically responding to.