Simple Select Query Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple Select Query Question

Author

Topic

MrQuestions
Starting Member

7 Posts

Posted - 2008-03-19 : 18:50:32

Hi,

I'm trying to return all of rows by applying a simple query to my database, however not all of the rows are being returned. The simple SQL query is:

SELECT `id` FROM `tags` WHERE `tagname`='baseball'

The rows that are left out are those in which the variable being searched for is not the first record listed in the table for a corresponding record. For example, the query of the "tags" table (below) for 'baseball' above returns only ids 10 and 12. id 11 also has a tag "baseball" but it is not being returned. What do I need to add to my query in order to return all of the ids that correspond, and not just the "first" ones? Thanks in advance!

table: tags
id | tagname
10 | baseball
10 | free
10 | stars
11 | fakeguy
11 | baseball
11 | free
12 | baseball
12 | test
12 | fantasy
12 | sports
13 | basketball
13 | hoops
13 | ncaa

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-03-19 : 22:23:12

You shouldn't need anything else...your query should return 10, 11 and 12. Are you sure there's not a typo or space in the tagname of the missing records?

MrQuestions
Starting Member

7 Posts

Posted - 2008-03-19 : 22:43:45

Yeah, there aren't typos, it's pretty strange... If I Query for "free" instead of "baseball" it returns no rows even though "free" is a tag assossiated with 11 and 10. It seems to only return the first record listed because if I query for "basketball" it will return 13 and if I query for "hoops" or "ncaa" it will return 0 rows.

At least I'm not going crazy... :)

Thanks for the reply

readysetstop
Posting Yak Master

123 Posts

Posted - 2008-03-20 : 06:58:10

Unable to duplicate this. Can you post your DDL?

____________________________________________________________________________________
"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-20 : 07:04:13

can you try your query with like instead of =

i.e.
select id
from tags
where tagname like '%free%'

if it returns the right data it would indicate that you have some whitespace characters or similar in the column as well

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-20 : 10:04:33

or
select id
from tags
where ltrim(rtrim(tagname)) = 'free'

MrQuestions
Starting Member

7 Posts

Posted - 2008-03-20 : 10:09:24

Hi,

Thanks for all the replies, I wish I could figure out what was going on...

I tried to use the "LIKE" statement instead of equals, as in:
SELECT `id` FROM `tags` WHERE `tagname` LIKE 'baseball'

It returned ids 10 and 12 but neglected to return 11 as in the previous query. What could be going on here?

I must have some trailing spaces in the data that is messing up the query, because it appears that 'baseball' is the value in the field of id 11, but it can't be... I made sure I wasn't going crazy by running an insert query to add some 'testtags' that I could be sure had no other invisible characters, and it worked fine. I'll get to the bottom of it...

Thank you all for helping this confused fool. :)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-20 : 10:18:15

if you use LIKE you need the %% as well .... LIKE '%baseball%'

Em

BSinatra
Starting Member

13 Posts

Posted - 2008-03-20 : 10:19:37

I think
SELECT `id` FROM `tags` WHERE `tagname` LIKE 'baseball'

should be
SELECT `id` FROM `tags` WHERE `tagname` LIKE '%baseball%'

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-20 : 11:02:06

are you using SQL Server? Get rid of those quotes around your table/column names. If you must delimit them, use [ and ]. This makes your code clearer and avoids the possibility that 'tagname' is being interpreted as a string literal, not a column name.

- Jeff
http://weblogs.sqlteam.com/JeffS

MrQuestions
Starting Member

7 Posts

Posted - 2008-03-21 : 23:51:44

I figured out what it was, there was an error in my input code making leading spaces. It's fixed now, thanks everyone.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources