Help writing database queries for derby?

Question

I have a database containing multiple tables (Person, Parents, etc)

Person table has certain attributes particularly ssn, countryofbirth and currentcountry.

Parents table has ssn, and fathersbirthcountry

The ssn in Person is the same ssn in Parents - that is how they're linked.

I'm trying to output the SSNs of all people who have the same countryofbirth as their fathersbirthcountry and also have same currentcountry as fathersbirthcountry.

SELECT Person.ssn 
FROM Person, Parents 
WHERE fathersbirthcountry = countryofbirth 
AND currentcountry = fathersbirthcountry;

the above doesn't seem to be working, could anyone please help me out?

Answer 1

You never mention how Person's stores its reference to Parents. I'll assume there is a MotherId and FatherId in the Person table so you would get:

Select SSN
From Person
Where BirthCountry =    (
                        Select BirthCountry
                        From Parents
                        Where Person.FatherId = Parents.Id
                        )

Now, this assumes BirthCountry in the Person table is the same list as BirthCountry in the Parents table.

Answer 2

You do not make clear what condition links a Person record with a Parent record. For this example, I am going to assume that Person contains an additional field, not mentioned by you, called FatherSSN. If so:

   SELECT Person.SSN 
   FROM Person, Parents
   WHERE Person.FatherSSN = Parents.SSN
     AND Person.CountryOfBirth = Parents.FathersBirthCountry
     AND Person.CurrentCountry = Parents.FathersBirthCountry

or, in SQL-92 JOIN syntax:

   SELECT Person.SSN 
   FROM Person INNER JOIN Parents
   ON Person.FatherSSN = Parents.SSN
     AND Person.CountryOfBirth = Parents.FathersBirthCountry
     AND Person.CurrentCountry = Parents.FathersBirthCountry

The two versions should yield the same result (and execution plan).

Finally, if this is your own database it could easily and profitably be refactored to feature only a single Person table holding all generations using exactly the same structure for that single table as you have now. If you make that restructuring, your SQL would look like this:

   SELECT P1.SSN 
   FROM Person P1 INNER JOIN Parents P2
   ON P1.FatherSSN = P2.SSN
     AND P1.CountryOfBirth = P2.CountryOfBirth
     AND P1.CurrentCountry = P2.CountryOfBirth