How to fetch only those rows that are not empty

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

SQL Server 2008 Forums

Transact-SQL (2008)

How to fetch only those rows that are not empty

Author

Topic

Abid

110 Posts

Posted - 2014-08-12 : 08:39:10

I have a little problem here.
I have a table Address, where i have fields:
ADdressID, CustomerAddress, SupplierAddress, CountryID, CityID, TownID

Every time I insert new record for Cust or for Suppli, so one column remains empty. Because when i insert new record of customer so the column of supplier remains empty and when i insert new record of supplier so column of customer remains empty. Now when I fetched the Customer Addresses on Customer Form, so the record appears like this in comboBox. My simple question is that How do i remove these empty rows. What would be my select query? THanks to you all. I wish I could upload the screen Shots of my problem.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-12 : 08:47:42

If by 'empty' you mean NULL, then this would do it

SELECT ...
WHERE SupplierAddress IS NOT NULL

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-12 : 08:53:07

First of all you have accept NULLS on these columns with no default.
If you want to return the rows where there is a NULL value in one of the columns
you can use
select
ISNULL([CustomerAddress],'No Customer'), -- sets a default
ISNULL([SupplierAddress],'No Supplier') -- sets a default
From Addresses

-- Or to remove the NULLS

Select * from Address
Where CustomerAddress IS NOT NULL
OR SupplierAddress IS NOT NULL

-- really your design should be 2 tables one for Cutomer and one for Supplier!

We are the creators of our own reality!

Abid

110 Posts

Posted - 2014-08-12 : 09:42:16

@gbritton
I applied this query:

select AddressID, CustomerAddress from Addresses where CustomerAddress is not null
but its not working, its showing the same result along with empty rows

@sz1
I also tried it
select * from addresses where CustomerAddress is not Null
but not helping. Please guide me further.

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-12 : 09:49:14

select *
from addresses
where [CustomerAddress] Is not Null
OR [CustomerAddress] <> ''

select *
from addresses
where [SupplierAddress] Is not Null
OR [SupplierAddress] <> ''

Depending what you want to filter out that is: or combine both to remove rows where nulls or blanks are in any of the 2 columns:

select *
from addresses
where [CustomerAddress] Is not Null
OR [SupplierAddress] Is not Null
OR [CustomerAddress] <> ''
OR [SupplierAddress] <> ''

We are the creators of our own reality!

Abid

110 Posts

Posted - 2014-08-12 : 10:08:46

Dear sz1. None of them is working. Its showing same result, which i don't need.

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-12 : 10:16:51

That sounds strange, you do mean that only one of the columns is always null or blank as you are only inserting into either CustomerAddress or SupplierAddress at any one time. Are you talking about rows where all columns are blank in any one row?. Are you running the query in Management Studio? or from an App.

We are the creators of our own reality!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-12 : 10:22:37

Abid,

Would you please post:

1. CREATE TABLE statements to create the tables involved in the query
2. INSERT INTO statements to populate the tables with test data.
3. Your query and the results you get when you run it against the test tables created in steps 1 and 2
4. The desired results from a properly-functioning query.

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-12 : 10:32:33

I agree with gbritton, also make sure your column names match ours, this should count the blanks for each but we need the structure.

Select
CountCustomerBlanks = Case
When [CustomerAddress] Is Null Or [CustomerAddress] = ''
Then Count(*)
End,
CountSupplierBlanks = Case
When [SupplierAddress] Is Null Or [SupplierAddress] = ''
Then count(*)
End
From Addresses

We are the creators of our own reality!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources