jadupl jadupl - 10 months ago 55
SQL Question

SQL Join with condition, several results

I have exercise for SQL using Library database, on picture below You can see how some of tables are connected.

enter image description here

I need to return addressess of those adult members of library who has kids(juvenile) born before 1996. I have prepared SQL statement.
Is this statement right? Probably not, there is option that addresses could be the same for various of members. I don't know how to aproach to this statement statement.

SQL Statement:

SELECT DISTINCT a.street,a.city,a.state,a.zip FROM adult AS a
JOIN juvenile AS j
ON a.member_no = j.adult_member_no
WHERE YEAR(j.birth_date) < 1996

Answer Source

This is your query:

SELECT DISTINCT a.street, a.city, a.state, a.zip
FROM adult a JOIN
     juvenile j
     ON a.member_no = j.adult_member_no
WHERE YEAR(j.birth_date) < 1996;

Assuming the column and table names are correct, there is nothing a priori wrong with it. It should do what you want.

Is it the best possible statement? No. The issue is that the query needs to do the join and then an aggregation (for the distinct). Another way to write the query is closer to the statement of the question:

FROM adult a 
              FROM juvenile j
              WHERE a.member_no = j.adult_member_no AND
                    YEAR(j.birth_date) < 1996

You can describe this query as: "Select adults who have a juvenile born before 1996."

Note that this version does not require select distinct (unless there are duplicates in the adult table, which is unlikely). Technically, this form of join is called a "semi-join". The "semi" is because it can stop at the first matching row. Once an appropriate juvenile is found for a given adult, no more need to be found.