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 |
|
iuvat
Starting Member
3 Posts |
 Posted - 2008-07-02 : 04:52:36
|
| Hey, I'm having problems getting the right information to display when the user searches the database. Searching works fine, but the way i'm filtering it keeps returning blank rows in the table, and rows with a blank in the item searched. I'm pretty new at this, and its being thrown together, so some of it is probably backwards. How can i just display results without blanks in the right places. Would it be better to add a check when i'm dealing out the data with coldfusoin for blanks?SELECT [Account Name] AS accName, Address1, [Contact Name], City, State, [Phone Number] AS phoneNumber, parent, [Zip Code] AS zipCode FROM tblCompanies WHERE (([Zip Code] LIKE '%#FORM.zipData#%') OR ([Zip Code] IS NULL AND '#FORM.zipData#' IS NULL)) AND ((State LIKE '%#FORM.stateData#%') OR (State IS NULL AND '#FORM.stateData#' IS NULL))Lots of it was cut out, but nothing different than whats here. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-02 : 05:22:23
|
| Do you mean eliminating blank and null values? |
 |
|
|
iuvat
Starting Member
3 Posts |
Posted - 2008-07-02 : 13:20:21
|
| I meant that when i search now, if i search a city, the results that have a different item blank such as a zip code or address don't come up. If i change what i have there and search a city, i get results where the city value is blank. I just need help getting them all to show up if you didn't search it weather its blank or not, but if you did search it, it should not show up with a blank value. My query has a few more "AND"s to filter it, which are the same as the one there. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-02 : 13:23:52
|
quote:
Originally posted by iuvat
I meant that when i search now, if i search a city, the results that have a different item blank such as a zip code or address don't come up. If i change what i have there and search a city, i get results where the city value is blank. I just need help getting them all to show up if you didn't search it weather its blank or not, but if you did search it, it should not show up with a blank value. My query has a few more "AND"s to filter it, which are the same as the one there.
did you meant this?SELECT [Account Name] AS accName, Address1, [Contact Name], City, State, [Phone Number] AS phoneNumber, parent, [Zip Code] AS zipCodeFROM tblCompaniesWHERE (([Zip Code] LIKE '%#FORM.zipData#%') OR ('#FORM.zipData#' IS NULL))AND ((State LIKE '%#FORM.stateData#%') OR ('#FORM.stateData#' IS NULL)) |
|
|
|
iuvat
Starting Member
3 Posts |
Posted - 2008-07-02 : 19:50:54
|
| If I do that, i dont get some rows which have blank spaces in other places. The full "WHERE" is here:WHERE (([Zip Code] LIKE '%#FORM.zipData#%') OR ('%#FORM.zipData#%' IS NULL )) AND ((State LIKE '%#FORM.stateData#%') OR ('%#FORM.stateData#%' IS NULL )) AND (([Account Name] LIKE '%#FORM.accountData#%') OR ('%#FORM.accountData#%' IS NULL )) AND ((Address1 LIKE '%#FORM.addressData#%') OR ('%#FORM.addressData#%' IS NULL )) AND ((City LIKE '%#FORM.cityData#%') OR ('%#FORM.cityData#%' IS NULL )) AND (([Phone Number] LIKE '%#FORM.phoneData#%') OR ('%#FORM.phoneData#%' IS NULL )) AND ((parent LIKE '%#FORM.parentData#%') OR ('%#FORM.parentData#%' IS NULL))For example, if i search seatttle in city, i get all the rows with full columns, but most of the rows have no parent value, or are missing a phone number. I need those to show up also. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-03 : 01:24:03
|
| Why are you putting % on beginning and end of params? thats not neededit should be ([Zip Code] LIKE '%#FORM.zipData#%') OR (#FORM.zipData# IS NULL )not [Zip Code] LIKE '%#FORM.zipData#%') OR ('%#FORM.zipData#%' IS NULL )i guess |
|
|
|
|
|
|
|
|
Help Parsing Tables