CODEWITHSUNDEEP
c#mysqlsql
Macka
Macka

Reputation: 20

SQL SELECT with JOIN and WHERE clause - selecting one record from two tables

I got something like this:

MySqlCommand sqlcmd = new MySqlCommand(@"SELECT Pupil.*, Instructor.* 
    FROM Pupil
    INNER JOIN Instructor ON Pupil.email = Instructor.email
    WHERE Pupil.email = '" + userEmail.Text + "'
      and Pupil.password = '" + userPassword.Text + "'", sqlconn);

and I need to get a record which is hidden in first or the second table. I checked and I inserted the same data into the second table. This SELECT is showing me the common record but I want only an unique from the first or the second table.

Upvotes: 0

Views: 72

Answers (1)

openshac
openshac

Reputation: 5155

Following your comment above I've modified the query. Here are two examples for the example data:

Pupils

Instructors

Query

/* Adam is a pupil but not an instructor */
select Pupil.*
from Pupil
left join Instructor on Pupil.email = Instructor.email
where Instructor.email is null
  and Pupil.email = '[email protected]'
union 
select Instructor.*
from Instructor
left join Pupil on Pupil.email = Instructor.email
where Pupil.email is null
  and Instructor.email = '[email protected]';

/* Chris is an instructor but not a pupil */
select Pupil.*
from Pupil
left join Instructor on Pupil.email = Instructor.email
where Instructor.email is null
  and Pupil.email = '[email protected]'
union 
select Instructor.*
from Instructor
left join Pupil on Pupil.email = Instructor.email
where Pupil.email is null
  and Instructor.email = '[email protected]';

You can find a working example at: http://sqlfiddle.com/#!9/c490c7/28

Upvotes: 1

Related Questions