group query - 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)

group query

Author

Topic

jai2808
Starting Member

27 Posts

Posted - 2009-05-26 : 09:36:51

Hi,
I need help in implementing the logic.
I have data in a table as shown below
RecID Fname LName Address SID
1 x Y abc 12
2 x Y xyz 12

I need to match address or SID and get the results set as in this case SID is same but different address, still i need to get these records.
In case if address is same and SID is different, i need both these records.
I need to check if address or sID is same and get the results wrt to fname and lname.
can some one help on this query.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 09:58:54

SELECT DISTINCT RecID Fname LName Address SID FROM Table1

E 12°55'05.63"
N 56°04'39.26"

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-26 : 10:27:35

Not sure what you want.
Looks like rows with the same SID but different addresses and same address but different SID.
Not sure what you mean by the rest but here's a start

select *
from tbl
where SID in
(select SID
from tbl
group by SID
having count(distinct address) > 1
)
or address in
(select address
from tbl
group by address
having count(distinct SID) > 1
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jai2808
Starting Member

27 Posts

Posted - 2009-05-29 : 05:06:09

Sorry for not giving the exact query
here is my requirement.
I have a table along with the following data
Fname Sname Address LegalID
A B xxx 0
A B xxx 2
A B yyy 2

I need to write a query where F name and S Name are same and addess or legalid should be matching.
The output should show all the 3 rows because, 1 and 2 record have same address and 2 and 3 record has legal id.
I just want the fname and sname.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 06:22:42

[code]-- Prepare sample data
DECLARE @Source TABLE
(
Fname VARCHAR(20),
Sname VARCHAR(20),
Address VARCHAR(20),
LegalID INT
)

INSERT @Source
SELECT 'A', 'B', 'xxx', 0 UNION ALL
SELECT 'A', 'B', 'xxx', 2 UNION ALL
SELECT 'A', 'B', 'yyy', 2

-- Prepare staging area
DECLARE @Stage TABLE
(
Fname VARCHAR(20),
Sname VARCHAR(20),
Address VARCHAR(20),
LegalID INT,
Match INT
)

insert @stage (fname, sname, [address], legalid)
SELECT fname, sname, [address], legalid
FROM @source

UPDATE s
SET s.Match = 1
FROM @Stage AS s
INNER JOIN (
SELECT Fname,
Sname,
[Address]
FROM @Stage
GROUP BY Fname,
Sname,
[Address]
HAVING COUNT(*) > 1
) AS d ON d.Fname = s.Fname
AND d.Sname = s.Sname
AND d.[Address] = s.[Address]

UPDATE s
SET s.Match = 2
FROM @Stage AS s
INNER JOIN (
SELECT Fname,
Sname,
LegalID
FROM @Stage
GROUP BY Fname,
Sname,
LegalID
HAVING COUNT(*) > 1
) AS d ON d.Fname = s.Fname
AND d.Sname = s.Sname
AND d.LegalID = s.LegalID

select * from @stage
where match is not null[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources