Combining Two Columns based on Certain Criteria

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

General SQL Server Forums

New to SQL Server Programming

Combining Two Columns based on Certain Criteria

Author

Topic

JohnGault777
Starting Member

31 Posts

Posted - 2011-03-18 : 14:24:48

I have two columns of that have addresses in them. I have two tables one that has the primary client contact and address and another that has the location information for the client. When the location information for the first location matches the primary client contact information the address has not values in it. So when I combine the two tables I get two sets of addresses where some of the time nothing shows up in the second set. Here's my example:

Table 1

ClientID ClientName PrimaryAddress
1 Joe Smith 123 Maple Ave
2 Dave Johnson 345 Main Street
3 Sally Davis 678 Backroad Ave

Table 2

LocationID ClientID LocationAddress LocationNumber
1 1 Null 1
2 1 678 Ruth Court 2
3 1 890 Johnson Street 3
4 2 Null 1
5 3 Null 1
6 3 900 Curlew Court 2

So when you combine the two:

Select ClientName, LocationID, PrimaryAddress, LocationAddress
From Table 1 a inner join Table 2 b
ON a.clientid = b.clientid

Output
ClientName LocationID PrimaryAddress LocationAddress
Joe Smith 1 123 Maple Ave Null
Joe Smith 2 123 Maple Ave 678 Ruth Court
Joe Smith 3 123 Maple Ave 890 Johnson Street
Dave Johnson 1 345 Main Street Null
Sally Davis 1 678 Backroad Ave Null
Sally Davis 2 678 Backroad Ave 900 Curlew Court

I want it to look like this:

Desired Output

ClientName LocationID LocationAddress
Joe Smith 1 123 Maple Ave
Joe Smith 2 678 Ruth Court
Joe Smith 3 890 Johnson Street
Dave Johnson 1 345 Main Street
Sally Davis 1 678 Backroad Ave
Sally Davis 2 900 Curlew Court

Does anyone know how to do this?

JG777

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-18 : 14:33:53

SELECT ClientName, LocationID, COALESCE(LocationAddress, PrimaryAddress) LocationAddress
FROM Table1 A
INNER JOIN Table2 B ON A.ClientID=B.ClientID

If you can redesign this, it's better to remove the PrimaryAddress from the Client table and keep all the address info in Location only, to avoid problems like this one.

JohnGault777
Starting Member

31 Posts

Posted - 2011-03-18 : 14:43:20

Ok I just tried it but I realized that there are some situations where the Primary Address actually shows up in the Location Address field. So sometimes it isn't Null. Sometimes there is a field. In addition, sometimes the Primary Address is a PO Box but the Location Address is the actually address of the location. Is there a way to say:

If they two addresses match, select Primary Address. If they don't match select Location Address. If Primary Address is like '%PO Box%', then select location address.

JG777

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-18 : 14:46:36

If they match then it doesn't matter which one you choose. COALESCE will only substitute PrimaryAddress if LocationAddress is NULL. The way it's currently written should solve your examples.

JohnGault777
Starting Member

31 Posts

Posted - 2011-03-18 : 15:17:08

But wouldn't it only bring the primary address? I need the Location Address if the it doesn't match the Primary Address or if the Primary Address is a PO Box.

Could I do the following:

Pull the Addresses where the Location Addresses are Null.
Then in a separate query pull location addresses when the Primary address is like '%PO BOX%'.
Then in a seperate query pull the location addresses Location Address when the it matches the Primary Address.
Then UNION all three queries together into one output.

JG777

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-18 : 15:35:09

No, as I said, it ONLY uses PrimaryAddress if the LocationAddress is NULL. Otherwise it uses LocationAddress. If you don't want to include P.O. Boxes:

SELECT ClientName, LocationID, COALESCE(LocationAddress, PrimaryAddress) LocationAddress
FROM Table1 A
INNER JOIN Table2 B ON A.ClientID=B.ClientID
WHERE COALESCE(LocationAddress, PrimaryAddress) NOT LIKE '%PO Box%'

JohnGault777
Starting Member

31 Posts

Posted - 2011-03-18 : 17:30:37

Oh i get it now. Thank you. I misread you early post.

JG777

JohnGault777
Starting Member

31 Posts

Posted - 2011-03-21 : 11:05:26

This would work if the field was actually Null all the time. However there are times when there is a blank character in the field. I found that Case works.

Case
When LocationAddress = '' then PrimaryAddress
When LocationAddress is Null then PrimaryAddress
When LocationAddress <> '' then LocationAddress
End as Location Address

This solved my problem.

JG777

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-21 : 11:09:29

You can also do:

Case
When LocationAddress = '' then PrimaryAddress
When LocationAddress is Null then PrimaryAddress
ELSE LocationAddress
End as Location Address

JohnGault777
Starting Member

31 Posts

Posted - 2011-03-21 : 11:50:46

Good call. Thank you.

JG777

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-21 : 21:13:33

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

I think your design is wrong. And it is not just silly names like “Table1”, it is non-normalization. A client is an entity. A location is an entity. They have a relationship, which needs its own table. A location is not an attribute any more than a book grows out of the chest of an author. That relation is authorship.

CREATE TABLE Clients
(client_id INTEGER NOT NULL PRIMARY KEY,
client_name VARCHAR(35) NOT NULL. ---USPS size
..);

Does your industry use a SAN (Standard Address Number)? All zeros is a dummy.

CREATE TABLE Street_Addresses
(san CHAR(10) DEFAULT '0000000000' NOT NULL PRIMARY KEY,
street_addr VARCHAR(35) NOT NULL,
city_name VARCHAR(20) NOT NULL,
state_code CHAR(2) NOT NULL,
zip_code CHAR(5) NOT NULL,
..);

Now here is the relationship.
CREATE TABLE Client_Locations
(client_id INTEGER NOT NULL
REFERENCES Clients(client_id),
san CHAR(10) NOT NULL
REFERENCES Street_Addresses(san),
location_priority INTEGER DEFAULT 0 NOT NULL
CHECK(location_priority >= 0),
PRIMARY KEY (client_id, san, location_priority),
..);

The lowest location priority value is the client's primary location. You can sort them, update them, as you wish. Here is one way

SELECT C.*, A.*
FROM Client_Locations AS L,
Clients AS C,
Street_Addresses AS A
WHERE A.san = L.san
AND C.client_id = L.client_id
AND L.location_priority
= (SELECT MIN(location_priority)
FROM Client_Locations
WHERE C.client_id = L.client_id);

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

JohnGault777
Starting Member

31 Posts

Posted - 2011-03-22 : 16:29:03

jcelko - The database referenced in the post above is not an actual database. I simply made everything up to boil it down to the concepts. In addition, I do not want to post the specifics of the database on which I work. When I wrote it out it looked like little tables but the formatting didn't carry to the post.

In addition, I do not have a Computer Science degree and I have only been working with SQL for about 7 weeks. I was kind of forced into a situation where I'm working well outside my knowledge base, so I apologize if I'm not posting in the appropriate format.

I currently have a stack of books about 3 feet high on my desk that I'm plowing through to learn this stuff. Once I get through event half of them, I'll think about hitting up the books you mentioned. I'm in a sink or swim situation and I'm just trying to keep my head above water.

JG777

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources