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.
Author |
Topic |
jatrix32
Starting Member
49 Posts |
Posted - 2010-09-08 : 09:28:16
|
I wrote a query that is supposed to tell me how many users have an email address in my table, based on their group and sub group.I have a main group called 'group_id'.There are many sub groups within this group called 'sub_group_id1', 'sub_group_id2' etc. All of this information resides within an account which is listed as 'account_id'.So I the structure looks like this:Acount_ID (integer) Group_ID (comma separated numbers ex. 4,6,7) sub_group_id1 (comma separated numbers ex. 4,6,7) sub_group_id2 sub_group_id3because I have many accounts in my table, I need to drill to this particular account, and then grab the group, and then the sub group and then check if the user has an email address.Here is the query I have written:SELECT COUNT(*) AS [Number of employees]FROM SUBSCRIPTIONWHERE (account_id = 119) AND (group_id = '% 1 %') AND (sub_group_id = '% 5 %') AND (sub_email <> NULL)My query is netting a record count of 0, and I know this is incorrect.What am I doing wrong? |
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-08 : 10:01:43
|
You're doing equality matches. The = ignores wildcards and looks for exact, literal matches. To use wildcards, you need LIKE.Additionally, consider normalising this table. Comma-delimited lists in columns are a clear sign that the table is not normalised and an unnormalised design leads to difficult queries (as it has here)--Gail ShawSQL Server MVP |
|
|
jatrix32
Starting Member
49 Posts |
Posted - 2010-09-08 : 13:17:22
|
I am still getting 0 results. I also had to check against an email address and a phone number. I want the results for all users that have registered email address (but does not have a registered phone number), that belong to account_id 19, in group 1, in sub_group LIKE 1.Here is my query:SELECT COUNT(*) AS Expr1FROM SUBSCRIPTIONWHERE (account_id = 119) AND (group_id = 1) AND (sub_group_id LIKE '%1%') AND (sub_email <> NULL) AND (sub_user_number = NULL)I should not be getting 0 results. Any help would be great. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
jatrix32
Starting Member
49 Posts |
Posted - 2010-09-08 : 13:56:25
|
This worked! Thank You !!! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|