CODEWITHSUNDEEP
sql
MiniGunnR
MiniGunnR

Reputation: 5800

How do I do an SQL query based on a foreign key field?

I have the following tables:

people:

id, name

parent:

id, people_id, name

I have tried the following:

SELECT * FROM people
  LEFT JOIN parent ON people.id = parent.people_id
  WHERE parent.name != 'Carol';

How do I find all the people whose parent's name is not Carol?

Upvotes: 0

Views: 105

Answers (6)

Parshuram Kalvikatte
Parshuram Kalvikatte

Reputation: 1646

`

Select * from people P
INNER JOIN parent PA ON PA.people_id = P._id
where PA.name not in ('Carol')

`

Difference between INNER JOIN and LEFT OUTER JOIN is 1) INNER JOIN bring only similar data between two table for ex if in people table parent_id table is nullable then it will not discard the complete row,but in case of LEFT OUTER JOIN it will bring all the rows from LEFT table as well as related table from right table.with all null in right joined row..

Upvotes: 0

Bibudha R Sahoo
Bibudha R Sahoo

Reputation: 65

First of all the table structure you have taken restrict the future growth. Like in future if you want to add parents of your parents then it wont work in this table structure. You can do like : id | parent_id | people_name

Here you can make parent_id null for the parent and add parent_id as id for those who have parent. Here to retrieve you have to use SELF join(join in the same table)

Upvotes: 0

Mansoor
Mansoor

Reputation: 4192

SELECT * FROM people WHERE EXISTS(SELECT 1 FROM parent WHERE people_id = id AND name <> 'Carol')

Upvotes: 0

Suman Bhandari
Suman Bhandari

Reputation: 91

First of all, why would you need two tables? why can't you have a single table named "Person" with ID,Name,ParentID columns

Where ParentID will be optional and reference the ID if it has got parent.

And run the following query

select * from PERSON where Name not like 'Carol%' and ParentID IS NOT NULL;

Upvotes: 0

Pramod
Pramod

Reputation: 195

If the two tables are to be queried by using Foreign Key.

  1. If you want to get all records from one table that have some related entry in a second table then use Inner join

    Select * from People INNER JOIN parent ON people.id = parent.people_id WHERE parent.name <> 'Carol'

  2. Similarly LEFT JOIN will get all records from the LEFT linked table but if you have selected some columns from the RIGHT table, if there is no related records, these columns will contain NULL

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

You can try below code

select people.name from people
inner join parent on people.id=parent.people_id
where parent.name not in ('Carol')

Upvotes: 2

Related Questions