Filter Data from join - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Filter Data from join

Author

Topic

darenkov
Yak Posting Veteran

90 Posts

Posted - 2009-03-14 : 10:02:38

Ok i am struggling with the following.

I have two tables - a COMPANY table, and an ADDRESS table. The company table lists a bunch of companies, and the ADDRESS table lists the respective addresses for each company. Each company can have one more addresses, and the address may be a postal address or a physical address (differentiated by the 'IsPostalAddress' field).

I have joined the tables and all of the address for each business are showing, however when returning the results I only want to see the Physical Address (easy enough), but I also want to show the postal addresses for the businesses that don't have a physical address. The total number of addresses should therefore be equal to the total number of businesses in the Companies table. Is there a tidy way to do this?

COMPANY
-------
CompanyID
CompanyName
DateCreated

ADDRESS
--------
Address
Postcode
Suburb
Region
Country
IsPostalAddress (bit)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-14 : 10:31:18

[code]Select C.*,(Case When AD.IsPostalAddress = 1 then AD.Postcode Else AD.Address end)as Address
from Company C inner join
Addresses AD
on AD.CompanyID = C.CompanyID[/code]

darenkov
Yak Posting Veteran

90 Posts

Posted - 2009-03-14 : 10:49:24

That doesn't work. Your query returns all of the addresses for each business (ie, both postal address and physical adddress), whereas I was looking to only retrieve one address for each business (namely the physical address, or the postal address if the physical address doesn't exist)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-14 : 13:01:37

quote:
Originally posted by darenkov

That doesn't work. Your query returns all of the addresses for each business (ie, both postal address and physical adddress), whereas I was looking to only retrieve one address for each business (namely the physical address, or the postal address if the physical address doesn't exist)

Can you provide some sample data and expected output?

darenkov
Yak Posting Veteran

90 Posts

Posted - 2009-03-14 : 13:54:33

[code]
here is some sample data. i've only put the important columns in.

COMPANY
----------------------------------
CompanyID | CompanyName | Phone |
-----------------------------------
1 Westhaus 94513866
2 ABX Logistix 93784988
3 AWT Group 94550399
4 Alimahek 94557958
5 Allied GT 95611532

ADDRESS
--------------------------------------------------------
AddressID | CompanyID | IsPostalAddress | Address |
--------------------------------------------------------
1 1 0 50 Kewdale Road
2 1 1 17 Mews Road
3 2 1 14 Ilda Road
4 3 0 2 Norlin Street
5 3 1 180 Hay Street
6 4 1 11 Harvest Terrace
7 5 0 Suite 15, 23 Plain Street

SAMPLE OUTPUT DATA
---------------------------------------------------
CompanyID | CompanyName | AddressID | Address |
---------------------------------------------------
1 Westhaus 1 50 Kewdale Road
2 ABX Logistix 3 14 Ilda Road
3 AWT Group 4 2 Norlin Street
4 Alimahek 5 11 Harvest Terrace
5 Allied GT 6 Suite 15, 23 Plain Street
[/code]
* Note that the output shows that after the tables are joined the Companies that have more than one address in the address table, will only show one address in the output (if the company has two addresses, the the non-postal address is shown, otherwise the postal address is shown if that is the only address available)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-14 : 14:25:45

[code]Select t.CompanyID,t.Companyname,MAX(Case when m.ispost = 0 then address Else address end) from Company t
inner join Address m
on t.ID = m.ID
Group by t.CompanyID,t.Companyname[/code]

darenkov
Yak Posting Veteran

90 Posts

Posted - 2009-03-14 : 22:34:38

thanks, works a treat!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-14 : 22:35:11

quote:
Originally posted by darenkov

thanks, works a treat!

You are most Welcome.

darenkov
Yak Posting Veteran

90 Posts

Posted - 2009-03-14 : 23:32:15

One slight problem, since I have tried adding extra columns, ie M.Suburb, it brings back more records than it is supposed to.

This is what I did:


Select t.CompanyID,t.Companyname,M.Suburb, MAX(Case when m.ispost = 0 then address Else address end) from Company t
inner join Address m
on t.ID = m.ID
Group by t.CompanyID,t.Companyname, M.Suburb

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-15 : 08:31:40

Select CompanyID,Companyname,Suburb
from
(Select T.CompanyID,T.Companyname,M.Suburb,
ROW_NUMBER() OVER (PARTITION BY T.CompanyID,T.Companyname ORDER BY (Case when m.ispost = 0 then address Else address end) Desc)
as ROWID from COMPANY t
inner join ADDRESS m
on t.COMPANYID = m.COMPANYID)Z
Where Z.ROWID =1

darenkov
Yak Posting Veteran

90 Posts

Posted - 2009-03-15 : 09:46:28

Thanks, but I am getting the error "The multi-part identifier "T.listingID" could not be bound.

I have added an extra table now so that each company can have one or more listings. I have applied your approach with the three tables below but I am still getting the above error. Maybe I am struggling with the Partition keyword in the context below. This is starting to hurt my brain. I should stick to .net development.


-----------
COMPANY
-----------

companyid   companyname
----------- -------------------------------------------
2           Nobles & Son Ltd
3           RDR Valves
4           Aardvark Explo Consultants Pty Ltd
5           ABIX Logistics
6           ACCR Corporate
7           ActIndustrial Catering Pty Ltd
8           Adelphinto Energy Ltd
9           Svitzerz
10          ADDT Security
11          Adtech FRP Pipelines
12          Advanced Well Tech
13          AGCC Industries Pty Ltd
14          APRA Group
15          Akr Kvarner
16          Azko Noble
17          Alan's Containers Pty Ltd


------------
LISTING
------------

listingid   companyid   telephone            fax                  
----------- ----------- -------------------- -------------------- 
1           2           0893585266           0894513866           
2           3           0893784688           0893784988           
3           4           0893869672           NULL                 
4           5           0893608300           0893608399           
5           6           0894705070           0894702608           
6           7           0894491600           0894491611           
7           8           0894801300           0892634688           
8           9           0893358444           0893353286           
9           10          0894798849           0894798899           
10          11          0892627000           0892493966           
11          12          0893273400           0893273444           
12          13          0894391934           0894391932           
13          14          0893205600           0892264126           
14          15          0894295810           0894295839           
15          16          0894947777           0894947772           
16          17          0894557339           0894557958           
17          18          0894550300           0894550399           


---------
ADDRESS
---------

companyaddress                                 suburb             ispostaladdress          listingID
--------------------------------------------  ------------------ -------------------------------
50 Kewdale Road                                Welshpool          0				1
21 River Road                                  Bayswater          0				2
Unit 8, 59 Dalkeith Road                       Nedlands           1				2
6 Glassford Road                               Kewdale            0				3
221 Mars Street                                Welshpool          0				4
Ground Floor, 88 Colin Street                  West Perth         0				5
17 Mews Road                                   Fremantle          1				5
1 Eyre Street                                  Belmont            0				6
345 Victoria Road                              Malaga             0				7
Level 2, 267 St Georges Terrace                Perth              0				8
15 Beach Street                                Kwinana            1				8
Level 8 15-17 William Street                   Perth              0				9
Unit 2 / 40 Tacoma Circuit                     Canning Vale       1				9
14 Ilda Road                                   Canningvale        0				10

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-15 : 10:18:57

Can you show what you tried?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-15 : 10:32:24

[code]Select CompanyID,Companyname,Companyaddress,Suburb
from
(Select T.CompanyID,T.Companyname,M.Companyaddress,M.Suburb,
ROW_NUMBER() OVER (PARTITION BY T.CompanyID,T.Companyname ORDER BY (Case when m.ispost = 0 then m.Companyaddress Else m.Companyaddress end) Desc)
as ROWID from COMPANY t
INNER JOIN LISTING L
ON t.COMPANYID = L.COMPANYID
INNER JOIN ADDRESS m
ON L.LISTINGID = m.LISTINGID)Z
Where Z.ROWID =1[/code]

darenkov
Yak Posting Veteran

90 Posts

Posted - 2009-03-15 : 10:53:33

hi sorry no i had an extra column name in the partition that shouldn't have been there.

thanks for showing me that though. it's pretty neat. way better than i started out yesterday (i had two sets of joins and the first was a 'select not in', and then i used a union).

i am going to try and integrate the paging now. will i safely be able to do this with the existing ROW_NUMBER() OVER functionality, or will i have to wrap everything and use a second Row_ NUMBER() OVER?use a second ROW_NU

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources