sql server new users: Query Help - multiple selects based on logic inserted into one table -- sql server new users

You're in the

sql server new users

group:

Query Help - multiple selects based on logic inserted into one table

Jim in Arizona
8/23/2007 10:22:57 AM

sql server new users:

I'm having trouble trying to creat a query that does several things.

I have two tables. One is called address the other customer.
Both tables have the same column names (mostly, for our purposes here).

The goal is to pull out all address related records (addr1, city, state,
zip) from the address table where type=2 and state='AZ' or state='IA'.
However, if any of those records contain a blank or null addr1 field, then I
have to select the same values from the customer table. So, my first select
statement simply looks like this.

select
addr1
,city
,state
,zip
from address
where type=2 and state='AZ'

I did do a query to get the records from the customer table if there is a
null or blank value in the addr1 column of the address table, like this. I
also had to get all recrods with a an active status of Yes ('Y') or Suspend
('S').

select
customer.addr1 as [Cust Address]
,customer.city as [Cust City]
,customer.state as [Cust State]
,customer.zip as [Cust Zip]
from customer
inner join address on address.idnum = customer.id
where address.type = 2
and address.state = 'AZ'
and customer.state = 'AZ'
and address.addr1 = ''
and (customer.active = 'Y' or customer.active = 'S')
OR (address.addr1 IS NULL)

In a few rare situations, the address was put into the addr2 line of the
customer table instead of addr1. So, in the case that the addr1 column of
the record is blank or null, I would need to select the addr2 column
instead, which I have no idea how to do.

The final goal would be to take the final data from all queries, put it into
one table with the fields addr1, city, state, and zip so it could then be
exported into a CSV file, which I'm pretty sure I could do with enterprise
manager. I guess I should mention that I'm working with an MS SQL 2000
server.

My biggest learning curve here is combining the statments into a whole using
the proper logic. I'm ok with simple selects with a simple join, but that's
where my (current) skill ends.

I'm thinking some kind of IF EXISTS type statements combined with IF ELSE
BEGIN END statements. I've never used this kind of logic yet successfully
(in SQL). I know that somewhere amogst all that, I'll have to use an SELECT
INTO statment to get the data into a new table for my CSV file export
operation later.

Any help or insight ... ?

TIA,
Jim

Re: Query Help - multiple selects based on logic inserted into one table

Jim in Arizona
8/23/2007 10:41:39 AM

I also forgot to mention that on my first query against the address table,
I'm also going to have to narrow that down by doing a join with the customer
table just to check to see if the account is active or on suspend
(customer.active = 'Y' or customer.active = 'S'). So, that would change that
first query below to:

select
address.addr1
,address.city
,address.state
,address.zip
from address
inner join customer on customer.id = address.idnum
where address.type = 2
and (address.state = 'AZ' OR address.state = 'IA')
and (customer.active = 'Y' OR customer.active = 'S')

Thanks,
Jim

[quoted text, click to view]

Re: Query Help - multiple selects based on logic inserted into one table

Jim in Arizona
8/23/2007 11:31:44 AM

I just found out that there is another level of complexity that I have to
throw into the mix.

There's an additional table called nn_modem. I'll have to check the d_custid
column of this table. If there is a number in that table that exists that
matches the customer.id number, then that data would need to be included
otherwise it would need to be excluded. So, any customer in the customer
table who's ID is not in the d_custid column o fthe nn_modem table would be
ignored.

Hmmm.

If exists
(select
customer.id
,nn_modem.d_custid
from nn_modem
inner join customer on customer.id = nn_modem.d_custid)
BEGIN
.....
END

I'm just guessing on that one. For some reason, that doesn't sound right to
me.

[quoted text, click to view]

Re: Query Help - Narrowed Down

Jim in Arizona
8/23/2007 12:39:40 PM

I've narrowed down my statements into these two.

The first one gets all rows from the customer table where the addr1 column
was empty in the address table.

select
customer.addr1
,customer.city
,customer.state
,customer.zip
from customer
inner join address on address.idnum = customer.id
where
address.type = 2
and (address.state = 'AZ' or address.state = 'IA')
and (customer.state = 'AZ' or customer.state = 'IA')
and (address.addr1 = '' or address.addr1 IS NULL)
and (customer.active = 'Y' or customer.active = 'S')

This next statement gets all records from the address table that are needed
(i'm hoping its accurate).

select
address.addr1
,address.city
,address.state
,address.zip
from address
inner join customer on customer.id = address.idnum
where customer.id in
(
select
customer.id
from customer
inner join nn_modem on customer.id = nn_modem.d_custid
)
and address.type = 2
and (address.state = 'AZ' OR address.state = 'IA')
and (customer.active = 'Y' OR customer.active = 'S')
and (LEN(address.zip) = 5 or LEN(address.zip) = 9)
-- I found that some of the zips were only 3 digits or
-- some other odd number like 7 digits.
and NOT address.addr1 IS NULL
and NOT address.addr1 = ''

So, I just need to combine them into one result set and then append the
select into statment so I can fill a new table with the data I need for CSV
export.

Thanks,
Jim

[quoted text, click to view]

Re: Query Help - RESOLVED

Jim in Arizona
8/24/2007 11:02:06 AM

Hello Andrea.

So, wow. That was much more complex than I was anticipating. Of course, I'm
still in the early learning phase of my SQL knowledge but I'll get there. I
am able to glean some of what I wanted to learn from the bottom portion
where you used the CASE WHEN statement(s), which I had not seen before. That
should be helpful for me in the future as I throw together more projects.

In the end, I used the two SQL scripts independantly and inserted into two
different tables, then exported each into a seperate CSV then combined the
two. It was more tedius than it had to be but it got the job done on time.

You wouldn't happen to know a way to script out the data export into a csv
file instead of using sql management studio (2005) to get the job done (by
right clicking on database then chosing tasks then export data)? It would be
nice to include that into a query so everything is just done with the click
of the Execute button. :o)

Thanks for your help on my queries. Your time is greatly appreciated.

Jim

[quoted text, click to view]

Re: Query Help - RESOLVED

TheSQLGuru
8/24/2007 5:18:15 PM

I recommend a book on beginning TSQL programming. WROX press has several.

--
TheSQLGuru
President
Indicium Resources, Inc.

[quoted text, click to view]

Re: Query Help - multiple selects based on logic inserted into one table

Andrea Montanari
8/24/2007 7:33:09 PM

hi Jim,
[quoted text, click to view]

something like?

SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.Customers (
Id int NOT NULL,
Name varchar(10) NOT NULL,
Addr1 varchar(10) NULL,
Addr2 varchar(10) NULL,
City varchar(10) NULL,
State varchar(10) NULL,
Active char(1) NOT NULL
);
CREATE TABLE dbo.Address (
Id int NOT NULL,
Name varchar(10) NOT NULL,
Addr1 varchar(10) NULL,
City varchar(10) NULL,
State varchar(10) NULL,
Type int
);
CREATE TABLE dbo.nn_modem (
Id int NOT NULL,
Data varchar (10) NOT NULL
)
GO
INSERT INTO dbo.nn_modem VALUES ( 1 , 'exists' ); -- yes
INSERT INTO dbo.nn_modem VALUES ( 2 , 'exists' ); -- yes

INSERT INTO dbo.Customers VALUES ( 1 , 'name', 'c_addr1', NULL, 'city',
'AZ', 'S' ); -- yes
INSERT INTO dbo.Customers VALUES ( 2 , 'name', 'addr', NULL, 'city', 'AZ',
'Y' ); -- yes
INSERT INTO dbo.Customers VALUES ( 3 , 'name', 'addr', NULL, 'city', 'AZ',
'N' ); -- no
INSERT INTO dbo.Customers VALUES ( 4 , 'name', NULL, 'c_addr2', 'city',
'AZ', 'Y' ); -- yes

INSERT INTO dbo.Address VALUES ( 1 , 'name', 'addr', 'city', 'AZ', 2 ); --
yes
INSERT INTO dbo.Address VALUES ( 2 , 'name', 'addr', 'city', 'AZ', 1 ); --
no
INSERT INTO dbo.Address VALUES ( 3 , 'name', '', 'city', 'AZ', 2 ); -- yes
but no, see customer
INSERT INTO dbo.Address VALUES ( 4 , 'name', NULL, 'city', 'AZ', 2 ); -- yes
and yes
GO
SELECT a.Id, a.Name
, CASE WHEN a.Addr1 IS NULL THEN
ISNULL(CASE WHEN c.Addr1 IS NULL THEN c.Addr2 END, NULL)
ELSE a.Addr1 END AS [Address]
, CASE WHEN m.Id IS NOT NULL THEN m.Data ELSE 'Missing nm_modem info' END
AS [nm_modem data]
FROM dbo.Address a
JOIN dbo.Customers c ON c.Id = a.Id
LEFT JOIN dbo.nn_modem m ON m.Id = a.Id
WHERE a.State = 'AZ'
AND a.Type = 2
AND ( c.Active = 'S' OR c.Active = 'Y');

GO
DROP TABLE dbo.Address, dbo.Customers, dbo.nn_modem;
--<-------------
Id Name Address nm_modem data
----------- ---------- ---------- ---------------------
1 name addr exists
4 name c_addr2 Missing nm_modem info

regards
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
--------- remove DMO to reply

Re: Query Help - RESOLVED

rhaazy
8/24/2007 8:56:58 PM

[quoted text, click to view]

Look into using the BCP utility. It is available via the command line
and can export and import to/from ms sql server and a variety of
output files (e.g. comma, tab, charcter delimited)

Re: Query Help - RESOLVED

Jim in Arizona
8/27/2007 10:12:24 AM

I actually have a few wrox beginning sql books that I'm going over.

I had also got a copy of the T-SQL Querying and T-SQL Programming books
(microsoft press, both on sql 2005). Although I've only glanced at the
programming book, I've found that the t-sql querying book is much more
advanced that I thought it would be. Well, if anything, I know it will be
useful later as my skills advance.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server new users group.

home · blog · groups

Questions? Comments? Contact the dn