Need to extract only some values from the table

[srathi]

Hello Everyone,

I am looking for some help again....

So my table has CompanyID, companyname, companytype, address, Phone, and Fax etc...

I want to extract records that only have phone no. and nothing else. My query is giving me all companies that have phone so it's including fax, address etc. which i don't to see. i want the query to return the companyids that have phone numbers only.

Any help on this much appreciated,

Thanks,
S

[michaelb]

Try this:

SELECT * from myTable WHERE Phone is not null AND
(CompanyID is null and companyname is null 
and companytype is null and address is null and Fax is null);
 

Depending on your situation (the database you use and how values are collected and inserted) you may need to replace
"field is null" with field = '' // two single quotes
and
"field is not null" with field != ''

[srathi]

thanks for the quick response. my table has over 300 fields so i would have to do the null or '' for each. is there a faster way to do this?

[michaelb]

Nothing I can think of.
Of course you can create a view and list all these columns only once, then just do a select from view Keep in mind that you are going to do a full table scan, so if your table is large it may take a while for this query to complete.

You may need to address this issue elsewhere, it does not seem reasonable that application allowed to insert these records on the first place.

[srathi]

Nothing I can think of.
Of course you can create a view and list all these columns only once, then just do a select from view Keep in mind that you are going to do a full table scan, so if your table is large it may take a while for this query to complete.

You may need to address this issue elsewhere, it does not seem reasonable that application allowed to insert these records on the first place.

yeah. thanks for the help!