CODEWITHSUNDEEP
mysqlselect
user3052443
user3052443

Reputation: 842

Select from two mysql tables using select 1

With the structure and data below, I want to return the color from table A if that item is not in table B. I'm not getting a good result even though table B does not contain the item. The problem seems to be the second select statement since it always returns 1 when ran by itself. I thought "select 1" returns the count of what it finds. if that is so, how can it always return 1? Is this not a good way to do this?

    CREATE TABLE A (
      id INT NOT NULL AUTO_INCREMENT,
      pid INT NOT NULL,
      color_name VARCHAR (24) NOT NULL,
    )

    CREATE TABLE B (
      id INT NOT NULL,
      pid INT NOT NULL,
    ) 

    A {1,2,red}
    B {2,2}

    select color_name from A where (select 1 from B where B.id = '1' and B.pid = '2')

Upvotes: 0

Views: 39

Answers (2)

Younes Keraressi
Younes Keraressi

Reputation: 21

Couldn't get you well,

SELECT color_name FROM A WHERE A.id NOT IN ( SELECT  id FROM B )

Upvotes: 0

forpas
forpas

Reputation: 164089

You are missing NOT EXISTS:

select color_name from A where NOT EXISTS (select 1 from B where B.id = '1' and B.pid = '2')

but maybe you want this:

select color_name from A where NOT EXISTS (select 1 from B where B.id = '1' and B.pid = A.pid)

or this:

select color_name from A where NOT EXISTS (select 1 from B where B.pid = A.pid)

if all you want is to find pids of A which do not exist in B.

Upvotes: 2

Related Questions