Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community!

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Posted July 20, 2013 5:15am by

SQL SELECT Help Please

I'm working on a web app that pulls data from a database, and there's something I need to do but don't know how.

It's quite complicated so bear with me.

I have two tables. One is the children table and is structured so that every child has a parent, except those that don't.

child_name  parent_id

Andrew      3
Bill        3
Clare       NULL
Don         2
Eric        1

Then there's the parents table that has a list of parents.

parent_id   parent_name

1           Audrey
2           Bert
3           Clare

To get a table of each child and the name of his/her parent, I use the following SELECT statement.

SELECT child_name, parent_name FROM children
LEFT OUTER JOIN parents ON children.parent_id = parents.parent_id;

IMPORTANT things to note:

Note that in the children table, Andrew and Bill are both children of Clare - because Clare's ID is 3.

Also note Clare is a member of the children table too, but her parent_id is NULL - meaning she has no parent of her own.

What I want to do:

Say that in my SELECT statement, I add on a WHERE clause. Consequently, Andrew, Bill and Clare might get excluded from the child_name column - depending on what is inside that WHERE clause.

What I want is for Clare to be selected from the child_name column only when none of her children are.

So if Andrew and Bill are selected, then Clare should be excluded from the child_name column - regardless of the conditions in the WHERE clause.

If Andrew is selected but Bill is excluded, Clare should be excluded from the child_name column too - regardless of the conditions in the WHERE clause.

But if Andrew and Bill are both excluded, Clare could be either selected or excluded from the child_name column - depending on the conditions in the WHERE clause.

I told you it was complicated.

Any ideas?

July 20, 2013 8:55am

Can you give a couple examples including the WHERE clause you would be running and the undesired results you end up with?

Definitely sound complicated because there are multiple scenarios and results, so I might have to set up a temp database on my local machine then outline your example and mess with it.

July 20, 2013 9:19am

Good question. What I've got is something like WHERE parent_id = 3 AND age > 18. Say that Andrew and Bill are under 18, so they don't get selected.

Clare doesn't get selected either, because her parent_id is NULL. But the parent_id is actually referring to her, so I want her to get selected in this case.

Then say I had the clause WHERE parent_id = 3 AND age > 10. Say that Andrew is over 10 but Bill's not. So Andrew gets selected. In this case I wouldn't want Clare to be selected because Andrew is selected.

July 22, 2013 1:28pm

Hi Carlo,

I think you should be using one table to store this data unless a Person can have more then 1 parent.

Person_ID | Name | Parent_ID | Age 1 Andrew 3 7 2 Bill 3 15 3 Clare Null 15

So based on the above table the below query should return the results you expect as per your examples above. Just change the values of the variables to test.

SET @PARENT_ID = 3;
SET @AGE = 18;

SELECT  CHILD.PERSON_NAME AS CHILD_NAME, PERSON.PERSON_NAME AS PARENT_NAME
FROM 
(
    SELECT *
    FROM person
    WHERE PARENT_ID = @PARENT_ID AND AGE > @AGE
    UNION ALL
    SELECT *
    FROM person
    WHERE PERSON_ID = @PARENT_ID
    AND PERSON_ID NOT IN 
                        (
                         SELECT PARENT_ID 
                         FROM PERSON 
                         WHERE PARENT_ID = @PARENT_ID AND AGE > @AGE
                        )
)CHILD LEFT JOIN PERSON ON CHILD.PARENT_ID = PERSON.PERSON_ID

2 Answers

July 26, 2013 7:09am

I figured it out for myself. I used an IN subquery inside of a NOT IN subquery inside of the main query, and it seems to return the right results. Jason Drummond's explanation helped me to understand the concept of subqueries, so hats off to you.

July 22, 2013 1:40pm

Sorry the table I was using looks like this:

Person_ID | Name    | Parent_ID | Age
1            Andrew    3          7 
2            Bill      3          15 
3            Clare     Null       15

July 23, 2013 9:34am

Your solution did not work for me. I got an SQL error saying it didn't recognize the column name, despite the column name being correct.

I'm not sure why I get the error, not least because I don't understand the query. Can you walk me through the query, in particular the bracketed queries, the UNION ALL bit and the NOT IN bit. I've never seen these elements of an SQL query before. Also why did you write some of the non-keywords in uppercase? If I was less confused I might be able to figure it out.

July 23, 2013 3:07pm

Sorry I didn't really explain this solution.

So to begin I first created a MYSQL Database called 'test' using MySQL Workbench.

I then ran the following commands to create the person table in the test database.

CREATE TABLE test.person (
  `Person_ID` int(11) NOT NULL,
  `Person_Name` varchar(200) DEFAULT NULL,
  `Parent_ID` int(11) DEFAULT NULL,
  `Age` int(11) DEFAULT NULL
);

I then ran the following commands to insert records into the person table.

INSERT INTO test.person VALUES (1, 'Andrew', 3, 8);
INSERT INTO test.person VALUES (2, 'Bill', 3, 15);
INSERT INTO test.person VALUES (3, 'Clare', Null, 8);

Now to confirm you have data in the person table run the following command.

SELECT * FROM test.person

You should have 3 rows returned.

I suspect the error you had with it not recognising the column name was due to the databases collation being case sensitive which means 'Person_Name' is not the same as 'PERSON_NAME' however to avoid this I have modified the query and reposted below.

With regard to your questions here are few key words you could search for on google for an explanation of each. 1) The queries in brackets are called Sub Queries, search for 'Example SQL Sub Queries'. Here is an example anyway.

SELECT *
FROM
(
        SELECT *
        FROM test.person
)A

2) The UNION ALL is a type of Join that essentially stacks the contents of one table on top of another. For example see the results of the below.

SELECT *
FROM test.person
UNION ALL
SELECT *
FROM test.person

You will have 6 records returned.

Note: There is a difference between UNION and UNION ALL. UNION will return a distinct set of rows from both tables as opposed to UNION ALL which will return all records.

3) NOT IN is used in the where clause to only return records if they don't exist. I've put a few examples below so you can see how it is used.

-- This will return all records where the Person ID is equal to 1 or 2.
SELECT *
FROM test.person
WHERE Person_ID IN (1,2)

Another example with a subquery.

-- This will return all records where the Person_ID exists in the sub queries result set. 
SELECT *
FROM test.person
WHERE Person_ID IN (
                                      SELECT Person_ID
                                      FROM test.person
                                      WHERE Parent_ID = 3
                                    )

So after understanding the above I hope the below query makes more sense.

SET @PARENT_ID = 3;
SET @AGE = 18;

SELECT  CHILD.Person_Name AS CHILD_NAME, Person.Person_Name AS PARENT_NAME
FROM 
(
    SELECT *
    FROM test.person
    WHERE PARENT_ID = @PARENT_ID AND AGE > @AGE
    UNION ALL
    SELECT *
    FROM test.person
    WHERE Person_ID = @PARENT_ID
    AND Person_ID NOT IN 
                        (
                         SELECT Parent_ID 
                         FROM test.person 
                         WHERE Parent_ID = @PARENT_ID AND Age > @AGE
                        )
)CHILD LEFT JOIN test.person ON CHILD.Parent_ID = test.person.Person_ID

July 26, 2013 7:13am

Your query still didn't make sense to me and I didn't end up using it, but your explanation helped me to figure out a solution. Thanks!

Posting to the forum is only allowed for members with active accounts.
Please sign in or sign up to post.

Welcome to the Treehouse Community

Looking to learn something new?

deletedaccount

deletedaccount

SQL SELECT Help Please

Chris Howell

Chris Howell

deletedaccount

deletedaccount

Jason Drummond

Jason Drummond

2 Answers

deletedaccount

deletedaccount

Jason Drummond

Jason Drummond

deletedaccount

deletedaccount

Jason Drummond

Jason Drummond

deletedaccount

deletedaccount