Difficult 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 2008 Forums

Transact-SQL (2008)

Difficult Join

Author

Topic

waldes
Starting Member

8 Posts

Posted - 2011-11-12 : 04:59:16

Hello!

i have table1 with buildings and their owners and table2 with buildings and their owners. Now I want to find out if there have been any changes
* between buildings (are there any new)
* between owners (has the owner changes or are there any new owners)
* between addresses (has the address of the owner changed)

The tables are connected with key = city+building_nr

table 1:
city; building_nr; owner; address
city1; 1; owner11; address11

table 2:
city; building_nr; owner; address
city1; 1; owner11; address11
city1; 1; owner12; adress12
city1; 2; owner21; address21

result table:
city; building_nr; table1.owner; table1.address; table2.owner; table2.address
city1; 1; owner11; address11; city1; 1; owner11; address11
city1; 1; NULL; NULL; owner12; adress12
city1; 2; NULL; NULL; owner21; address21

do you have any ideas, i can not solve this problem on my own!???

thanks a lot!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-12 : 05:37:28

[code]
SELECT COALESCE(t1.city,t2.city) AS city,
COALESCE(t1.building_nr,t2.building_nr) AS building_nr,
t1.owner as table1owner,
t1.address as table1address,
t2.owner as table2owner,
t2.address as table2address
FROM table1 t1
FULL OUTER JOIN table2
ON t1.city = t2.city
AND t1.building_nr = t2.building_nr
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

waldes
Starting Member

8 Posts

Posted - 2011-11-12 : 08:57:04

wow, if this will work i would be very happy ;-)
i will test it when i have access to my laptop and i will give you an reply

thanks a lot, greets waldes!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-12 : 11:59:10

ok...no problems

let us know how you got on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

waldes
Starting Member

8 Posts

Posted - 2011-11-12 : 17:29:20

hey, I have made a test and i have noticed that there is still a problem.

In table1 there is just one owner, in table2 there are 3 owners. after joining the talbes the owner from table1 is listed 3 times, but it should be listed just one time. the other 2 rows should be filled with NULL-values.

how can i do this? thanks for the support!!

cheers waldes!

waldes
Starting Member

8 Posts

Posted - 2011-11-13 : 07:40:16

quote:
Originally posted by visakh16

ok...no problems
let us know how you got on

do you have any idea for my problem?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-11-13 : 10:07:14

Try this:


SELECT T2.city, T2.building_nbr,
       CASE WHEN T1.owner = T2.owner THEN T1.owner ELSE NULL END AS [table1.owner],
       CASE WHEN T1.address = T2.address THEN T1.address ELSE NULL END AS [table1.address],
       T2.owner, T2.address
  FROM table1 AS T1
       RIGHT OUTER JOIN
       table2 AS T2
       ON T2.city = T1.city
          AND T2.building_nbr = T1.building_nbr

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-13 : 11:55:29

quote:
Originally posted by waldes

hey, I have made a test and i have noticed that there is still a problem.

In table1 there is just one owner, in table2 there are 3 owners. after joining the talbes the owner from table1 is listed 3 times, but it should be listed just one time. the other 2 rows should be filled with NULL-values.

how can i do this? thanks for the support!!

cheers waldes!

sounds like a presentation issue. is this a reporting requirement? if yes, you should be trying to do it at front end to suppress repeating values (owner from first table). its called hide duplicates property in SSRS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

waldes
Starting Member

8 Posts

Posted - 2011-11-13 : 12:30:17

yes, it is an requirement and a manipulation in the front end (do you mean Excel?) is not possible, because there are two many datarows ;-)

is there a possibility to change the sql-statement?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-13 : 12:33:10

if you want to do it in sql try previous suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

waldes
Starting Member

8 Posts

Posted - 2011-11-13 : 12:38:04

quote:

Originally posted by malpashaa

Try this:


SELECT T2.city, T2.building_nbr,
       CASE WHEN T1.owner = T2.owner THEN T1.owner ELSE NULL END AS [table1.owner],
       CASE WHEN T1.address = T2.address THEN T1.address ELSE NULL END AS [table1.address],
       T2.owner, T2.address
  FROM table1 AS T1
       RIGHT OUTER JOIN
       table2 AS T2
       ON T2.city = T1.city
          AND T2.building_nbr = T1.building_nbr

This works well, but i have the following problem:

i have 2 owners in table1 and the same 2 owners in table2. but the result of the sqlstatement is the following:

city; building_number; t1.person_name; t1.address; t2.person_name; t2.address;
NY; 1; t1.Person1; t1.Adress1; t2.Person1; t2.Address1;
NY; 1; NULL; NULL; t2.Person1; t2.Address1;
NY; 1; NULL; NULL; t2.Person2; t2.Address2;
NY; 1; t1.Person2; t1.Adress2; t2.Person2; t2.Address2;

as you see, there is following problem: line1 + line 4 are ok, but line2 and line3 should not be listed?

do you have any ideas?

Thanks a lot for the support?

waldes
Starting Member

8 Posts

Posted - 2011-11-13 : 12:40:21

quote:
Originally posted by visakh16

if you want to do it in sql try previous suggestion

you are right, i tried the malpashaa´s suggestions, this works good but still i have the problem, which i have posted?

thanks for the support ;-) do you have an idea?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-13 : 12:42:04

[code]
SELECT COALESCE(t1.city,t2.city) AS city,
COALESCE(t1.building_nr,t2.building_nr) AS building_nr,
t1.owner as table1owner,
t1.address as table1address,
t2.owner as table2owner,
t2.address as table2address
FROM table1 t1
FULL OUTER JOIN table2
ON t1.city = t2.city
AND t1.building_nr = t2.building_nr
AND t1.owner=t2.owner
AND t1.address = t2.address
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

waldes
Starting Member

8 Posts

Posted - 2011-11-13 : 12:58:52

quote:

Originally posted by visakh16


SELECT COALESCE(t1.city,t2.city) AS city,
COALESCE(t1.building_nr,t2.building_nr) AS building_nr,
t1.owner as table1owner,
t1.address as table1address,
t2.owner as table2owner,
t2.address as table2address
FROM table1 t1
FULL OUTER JOIN table2
ON t1.city = t2.city
AND t1.building_nr = t2.building_nr
AND t1.owner=t2.owner
AND t1.address = t2.address

hey, I have adapted this sqlstatement, because my real db looks a little bit different. I would like to use your sql-statement, but i always get the following message:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "city" could not be bound.

City looks like "Horní Pocernice 643777" and so on. This message 4104 also ocurrs with all of the datafields like address, person_name etc.

Do you know this problem????

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 00:27:51

i missed an alias


SELECT COALESCE(t1.city,t2.city) AS city,
COALESCE(t1.building_nr,t2.building_nr) AS building_nr,
t1.owner as table1owner,
t1.address as table1address,
t2.owner as table2owner,
t2.address as table2address
FROM table1 t1
FULL OUTER JOIN table2 t2
ON t1.city = t2.city
AND t1.building_nr = t2.building_nr
AND t1.owner=t2.owner
AND t1.address = t2.address

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources