MySQL Question

abdul_zu · 08-30-2008, 08:40 AM

Hi All,

i have tow table soldlots and clientinf i need to get the ID from clientinf if the same ID is not exits in soldlots. I tried following query but it is not returning correct result.

for the example the id_lot have 3 id_client and one is already in soldlots but when i run the following query it is displying whole 3 records, it must display only two and ignore the one which already have duplicate in soldlots.

PHP Code:



select clientinf.id_client as cids from clientinf left join soldlots on clientinf.id_client != clientinf.id_client
where clientinf.id_lot = 44

Pls advise me how i can make the correct query.

Thank You

zaichik · 08-30-2008, 09:32 AM

If you are using MySQL 4.1 or greater, you can use a subquery:

Code:

mysql> create table clientinf ( id_client int(11), desc_client varchar(50) );
Query OK, 0 rows affected (0.00 sec)

mysql> create table soldlots ( id_lot int(11), id_client int(11), desc_lot varchar(50) );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into clientinf values (1, 'Client 1'), (2, 'Client 2'), (3, 'Client 3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into soldlots values (1, 1, 'Sold to Client 1'), (2, 1, 'Also sold to Client 1'), (3, 3, 'Sold to Client 3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from clientinf where id_client not in (select id_client from soldlots);
+-----------+-------------+
| id_client | desc_client |
+-----------+-------------+
|         2 | Client 2    |
+-----------+-------------+
1 row in set (0.01 sec)

mysql>

That doesn't match up exactly with the query you posted, but I'm not quite sure I'm following what is stored in each table. If you need more help, it would probably be a good idea to post the results of "show create table" or "describe" for clientinf and soldlots.

Hope that helps.