Search code examples
mysqlselectstatements

Comparative select on the same table (mysql)

I'm really newbie with mysql and i'm tryin to do a simple select, but I can't figure it out.

Thats what I have:

I got this in a Table named Control:
| CODE | OFFICE |
|  1   |   usa  |
|  2   |   usa  |
|  3   |   usa  |
|  4   |   usa  |
|  5   |   usa  |
|  1   | china  |
|  3   | china  |
|  4   | china  |

And I need get this:

| CODE | OFFICE |
|  2   |   usa  |
|  5   |   usa  |

Then, SELECT code,office WHERE the codes still aren't registered with office = china.

I have to make a self join or something like that or use GROUP BY statement ? I'm stuck here... I really would appreciate any help.

Solution

  • I guess this is working

     create table Test(id integer, code integer, office varchar(100));

insert into Test(id, code, office) values(1, 1, "usa"),(2, 2, "usa"),(3, 3, "usa"),(4, 4, "usa"),(5, 5, "usa"),(6, 1, "china"),(7, 3, "china"),(8, 4, "china");

select * from Test;

Select * from Test where code NOT IN (select code from Test where office =   "china");

    You have use sub query.