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

Jan Lundeen
Jan Lundeen
5,881 Points
Posted by Jan Lundeen
Jan Lundeen
5,881 Points

Issue - creating list of all fruits and vegetables starting with letters A through K (Querying Relational Databases).

Hi, In the Querying Relational Databases course, I'm trying to create a list of of all fruits and vegetables starting with letters A through K and running into some issues.

Here's the question: Challenge Task 2 of 6

There are two tables Fruit and Vegetable table. The Fruit table has a FruitID and a Name column and the Vegetable table has a VegetableID and Name column. Create a list of all fruits and vegetables starting with the letters A through K . In other words all fruit and vegetables that don't start with the letter L to Z.

Here's my solution:

SELECT Name FROM Fruit Where Name LIKE a%,b%,c%,d%,e%,f%,g%,h%,i%,j%,k% UNION SELECT Name FROM Vegetable Where Name LIKE a%,b%,c%,d%,e%,f%,g%, h%,i%,j%,k%;

I got the following error:

SQL Error: near "," : syntax error

I was also thinking of using IN to create a list. However, I'm not sure if I can use the % with IN.

Any ideas?

Thanks,

Jan

2 Answers

Steven Parker
Steven Parker
229,744 Points
Steven Parker
Steven Parker
229,744 Points

You can't use LIKE with IN.

And you need to enclose your strings in quotes. Also you will need to make each of your comma-separated filters complete expressions:

... WHERE Name LIKE "a%", Name LIKE "b%", /*...etc...*/

If that seems too wordy, give it some thought and perhaps you can come up with another approach that can be implemented more concisely.

Jan Lundeen
Jan Lundeen
5,881 Points
Jan Lundeen
Jan Lundeen
5,881 Points

Okay, so I can't combine LIKE with IN. That does make it really wordy (and prone to error if I type something incorrectly). However, I can't think of anything else better that will work. There's got to be an easier way to do it.

Thanks,

Jan

Steven Parker
Steven Parker
229,744 Points
Steven Parker
Steven Parker
229,744 Points

Hint: You can use inequality operators with strings. The strings compare based on alphabetical order. So, for example the "<" symbol means "less than" for numbers but for strings it means "comes earlier alphabetically".

Jan Lundeen
Jan Lundeen
5,881 Points
Jan Lundeen
Jan Lundeen
5,881 Points

Hi Steven,

Thanks for the hint. I wasn't sure I could do that. It sounds like it's a lot easier. For example, I could write the following query:

SELECT Name FROM Fruit Where Name < L UNION SELECT Name FROM Vegetable Where Name < L;

I tried this query. However, I got the following error:

SQL Error: no such column: L.

Any ideas regarding how to correct this?

Jan

Steven Parker
Steven Parker
229,744 Points
Steven Parker
Steven Parker
229,744 Points

You're almost there. You just need quotes around your strings.

SELECT Name FROM Fruit WHERE Name < "L" UNION SELECT Name FROM Vegetable WHERE Name < "L";
Jan Lundeen
Jan Lundeen
5,881 Points
Jan Lundeen
Jan Lundeen
5,881 Points

Hi Steven,

Thanks! That worked.

Jan

Kam Nejad
Kam Nejad
5,649 Points
Kam Nejad
Kam Nejad
5,649 Points

SELECT Name from Fruit WHERE SUBSTR(Name, 0) < "L" UNION SELECT Name from Vegetable WHERE SUBSTR(Name, 0) < "L";