CODEWITHSUNDEEP
phpmysqlsql
user2731263
user2731263

Reputation: 55

How to get the correct sql value on php

How to get the correct sql value from this table on php

I have two tables below;

Table: A

StateID   StudentID   Attendee
---------------------------------
ITB001      10          John
ITB001      20          Bob
ITB001      40          Mickey
ITB001      60          Jenny
ITB001      30          James
ITB001      70          Erica

Table: B

StateID   StudentID    Attendee
---------------------------------
ITB001       10          John
ITB001       30          James

I want to select and output Attendee value from Table A where is minus Table B. If Attendee from Table B have value John and James if so it will list Attendee value from Table A and only output without John and James on Table A list. So final output will be:

StateID   StudentID   Attendee
---------------------------------
ITB001      20          Bob
ITB001      40          Mickey
ITB001      60          Jenny
ITB001      70          Erica

Any help and hints would be appreciated. Thanks.

Upvotes: 0

Views: 60

Answers (3)

Justin Bell
Justin Bell

Reputation: 396

If I understand correctly, you want everything from table A that isn't already in table B. That would be possible using a LEFT JOIN:

SELECT A.*
    FROM A
        LEFT JOIN B
            ON A.StudentID = b.StudentID
                AND A.StateID = b.StateID
    WHERE B.StudentID IS NULL;

The [outer] left join lets you query for a full record set from the left operand, and partial from the right operand.

Upvotes: 0

M.Ali
M.Ali

Reputation: 69554

SELECT *
FROM TableA A
WHERE NOT EXISTS (SELECT 1
                  FROM TableB
                  WHERE Attendee = A.Attendee)

Upvotes: 1

ka_lin
ka_lin

Reputation: 9442

You can do this by:

Select * from A where StudentID  not in (select StudentID from B where 1=1)

Upvotes: 1

Related Questions