A a little difficult question for me.

In SQL:

We have a table_A with the fields 'nick' and 'old' and tabla_B with the fields 'nick' and 'city'.
I would like to select all the registries of table_A whose nick does not appear in the field 'nick' of table_B.
I do not know if I have explained it well. Which I want is to see that nicks of table_A does not appear in table_B.

Anticipated thanks.

A greeting.

 

talle

SELECT * FROM table_A OUTER JOIN table_B ON table_A.nick = table_B.nick WHERE table_B.nick = NULL;

Should do the trick.

 

Thanks, but don't work. Syntax error. I think "OUTER" not work.

 

in Draculus sollution - try left join that should work.
Expat

 

With "left join" does not show a syntax error, but it either does not show the wished result to me. It show zero results and I am completely safe that that is not correct.

 

You don't want to use table_B.nick = NULL because if nick does not exist it will not match table_B.nick, you need to use * table_B.nick != '' * or * table_B.nick <> '' * or table_B.nick IS NOT NULL *because these test values from the tables returned scan and not the table it's self. I used * table_B.nick <> '' * because it's a touch faster than the other (2)

So it would be like this...

SELECT t_a.* FROM table_A AS t_a LEFT JOIN table_B as t_b ON(t_a.nick = t_b.nick) WHERE t_b.nick <> '' GROUP BY t_a.nick;

PHP:

Where you would change...

table_A => change to your table name that holds ('nick' and 'old');
table_B => change to your table name that holds ('nick' and 'city');

Yo can also remove the *(SPACE)GROUP BY t_a.nick* if table_B does not contain rows with duplicates (nicks)!

jb

 

This works fine, but I obtain the opposite result. This show the registries of table_A with nick is present in table_B.

I want:

SELECT * FROM table_A ...(where nick is not present in table_B.nick)

 

Oh, sorry!.... (backwards logic)

SELECT t_a.* FROM table_A AS t_a LEFT JOIN table_B as t_b ON(t_a.nick = t_b.nick) GROUP BY t_a.nick HAVING COUNT(t_b.nick) = 0;

PHP:

Where you would change...

table_A => change to your table name that holds ('nick' and 'old');
table_B => change to your table name that holds ('nick' and 'city');

jb

 

OOoooHH!!!! Excellent!!!!!

You're a goob friend.

Thanks you very very very much.

Greetings from Spain.

 

Excuse me... A new question...

With DELETE don't work?

 

What do you want to DELETE?

jb

 

All the results of SELECT, directly, avoiding SELECT.

 

DELETE FROM table_a WHERE nick NOT IN ( SELECT nick FROM table_b GROUP BY nick );

PHP:

jb

 

Opss... a syntax error on 'SELECT nick FROM table_b GROUP BY nick'

(MySql v4.0.24)

 

Anytime you ask a database question be sure to put your version as things are not supported from version to version! It will save you and the one helping you much time...

This should work for you...

4.0.2 and up (2 examples select and delete)

// select

SELECT * FROM table_a LEFT JOIN table_b ON ( table_a.nick = table_b.nick ) WHERE table_b.nick IS NULL;

// delete

DELETE FROM table_a USING table_a LEFT JOIN table_b ON ( table_a.nick = table_b.nick ) WHERE table_b.nick IS NULL;

PHP:

jb

 

Fantastic!!!... You're the one!!!

Thanks you very much!!!

And excuse me for all.