CODEWITHSUNDEEP
phpmysqlsqldatabase
urhydy
urhydy

Reputation: 27

Selecting table rows from one table where value of table column in another table is x

I am trying to SELECT rows from a table where it has got a foreign key on second table and the key from the second table is used in a third table as foreign key. How can I retrieve the rows from the first table where on the third table doesn't have the key from the second table which this particular key's row has got the first tables' row's primary key as foreign key and also on the third table it hasn't got the foreign key of the fourth table.

I tried using inner join but it only works with SELECTING rows where it has got a specify value rather than not having this specify value.

Please help me

Faculty table

FaculID | FaculName | FaculLocation |

Course table

CourseID | CourseName | CourseDescription

FacultyCourse table

fcID | CourseID | FaculID

Registeration table

RegID | fcID | stuID

Student table

stuID | stuName | stuAge | stuAddress

so basically what i want to do now is get all bid where the e table hasn't got uid and did(which has got a bid foreign key).

Upvotes: 0

Views: 507

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13248

Try:

(change table and column names as appropriate)

updated based on example layout

select *
  from faculty
 where faculid not in (select faculty.FaculID
                         from faculty
                         join facultycourse 
                           on faculty.FaculID = FacultyCourse.FaculID
                         join registration
                           on registration.fcid = FacultyCourse.fcid
                        where registration.stuid = 'XYZ');

Upvotes: 0

Ganz
Ganz

Reputation: 169

Try to use LEFT OUTER JOIN:

SELECT a.*
FROM a
LEFT OUTER JOIN b on a.keya = b.keybjoina
LEFT OUTER JOIN c on b.keybjoina = c.keycjoinb
WHERE c.keycjoinb IS NULL

Upvotes: 3

Related Questions