Need help with a query against bad designed db model

Transact-SQL
1

Hi!

I'm having problems coming up with t-sql to solve my problem. The db itself isn't well designed from the beginning so that's why I'm struggling here.

I have two tables Guardian and Application. Guardian is a parent. In the Application we have the name AND the address of the child. The tables are joined by GuardianID.

One Guardian can have more than one Application (children). The address information is stored on the Application not the Guardian. My client want the name from the Guardian and the address from the Application.

This works fine if all children for that Guardian have the same address (distinct). What I need is a sql that retrieves Children with the same parent that have different addresses. The children can live at different addresses but I also want to find errors when they have entered the addresses.

Please help, anyone? :slight_smile:

Guardian
GuardianID int
FirstName varchar(50)
LatName varchar(50)

Application
ApplicationID int
GuardianID int
FirstName varchar(50)
LastName varchar(50)
StreetAddress varchar(50)
City varchar(50)
ZipCode varchar(10)
State varchar(50)

Guardian
GuardianID FirstName LastName
100 Carl Owen

Application
ApplicationID	GuardianID FirstName	LastName	StreetAddress 	City			ZipCode State
10				100	       Lisa		    Owen	    23 Main Street 	New York	    32233	NY
11              100		   John		    Owen		23 Main Streeet	New York	    32233	NY
2 
SELECT  g.*, a.*
FROM (
    SELECT GuardianID
    FROM Application
    GROUP BY GuardianID
    HAVING COUNT(DISTINCT StreetAddress + City + ZipCode) > 1
) AS dup_addrs
INNER JOIN Guardian g ON g.GuardianID = dup_addrs.GuardianID
INNER JOIN Application a ON a.GuardianID = g.GuardianID
ORDER BY g.GuardianID, a.ApplicationID
1 Like
3

Thanks!!! It works, great stuff.