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! While you're at it, check out some resources Treehouse students have shared here.

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

Databases

Ari Misha
Ari Misha
19,323 Points
Posted by Ari Misha
Ari Misha
19,323 Points

I cant seem to figure out this one. Help me!

Hiya guys! Please help me out with this one. I cant seem to figure out this one. I tried it with Regular Expressions but it doesnt seem to be working. Any ideas?

Here is my code:

SELECT Name FROM Fruit 
UNION
SELECT Name FROM Vegetable
WHERE Name LIKE '^[A-K]';

3 Answers

Stamos Bolovinos
Stamos Bolovinos
4,320 Points
Stamos Bolovinos
Stamos Bolovinos
4,320 Points

Hi Ari

You can't use this Regex Syntax in SQLite. Here you can find the valid SQLite LIKE Clause syntax.

In MSSQL, there is more functionality in the T-SQL LIKE Clause.

To make your query work in SQLite, use

SELECT Name FROM Fruit 
WHERE Name NOT BETWEEN 'A' and 'K'
UNION
SELECT Name FROM Vegetable
WHERE Name NOT BETWEEN 'A' and 'K';

Note, that this doesn't include Names beginning with the letter K. To match those Names to you would need to use 

SELECT Name FROM Fruit
WHERE Name NOT BETWEEN 'A' and 'L' 
UNION
SELECT Name FROM Vegetable
WHERE Name NOT BETWEEN 'A' and 'L';

Your example could work in T-SQL (although I can't test this now) probably like this:

SELECT Name FROM Fruit 
WHERE Name LIKE '[^A-K]%'
UNION
SELECT Name FROM Vegetable
WHERE Name LIKE '[^A-K]%';
Ari Misha
Ari Misha
19,323 Points
Ari Misha
Ari Misha
19,323 Points

Hiya Stamos! Thanks for explainin' it so well! And i didnt know "Regular Expression" is outta scope of SQLite. The hell, i didnt even know they use SQLite on treehouse. One more question. Why "NOT BETWEEN"? I mean the challenge is askin' me to look for entries that starts with "A" to "K".

This is the challenge link. and Btw i tried your way and it didnt work.

https://teamtreehouse.com/library/querying-relational-databases/set-operations/set-operations

Stamos Bolovinos
Stamos Bolovinos
4,320 Points
Stamos Bolovinos
Stamos Bolovinos
4,320 Points

Hi Ari

I have used NOT BETWEEN because of your example in the question. That looked like you want to exclude names starting with letters A trough K, as the ^ character is used that way in T-SQL.

For the challenge, you must use:

SELECT Name FROM Fruit 
WHERE Name BETWEEN 'A' AND 'L'
UNION
SELECT Name FROM Vegetable
WHERE Name BETWEEN 'A' AND 'L';
Stamos Bolovinos
Stamos Bolovinos
4,320 Points
Stamos Bolovinos
Stamos Bolovinos
4,320 Points

Don't forget also to add the WHERE clause to both SELECT statements. The WHERE clause is executed before the UNION, so if you add only one WHERE clause at the end of your statement, it will apply only to the second SELECT query.

Ari Misha
Ari Misha
19,323 Points
Ari Misha
Ari Misha
19,323 Points

Hiya Stamos! It worked. Thank you very much! All I had to do is add "WHERE" clause to both queries(i was adding WHERE clause to only one query before) , just like ya suggested me to. (: