record matching - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

record matching

Author

Topic

annamaria
Starting Member

30 Posts

Posted - 2008-02-03 : 06:18:56

review

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-03 : 07:05:22

Probably this is what you are looking at:-

select A, B, C
from table1 G1
INNER JOIN (SELECT DISTINCT A,B,C FROM table2) G2
ON G1.A = G2.A
and G1.B = G2.B
and G1.C = G2.C

If not, please post structure of table, sample data from them and expected result for quick solution.

To attribute the records a unique number, you have a function called ROW_NUMBER() in sql 2005. However, if you are using SQL 2000 then you might need to generate it yourself by means of subquery.

An index is actually used to speed up searching/retrieval of records from a table (just like index page in book). There are basically two types of indexes-clustered & non clustered. You can have only 1 clustered & any number of non clustered indexes in a table. The clustered index causes table to be physically sorted on indexed column while non-clustered index has a seperate storage location which points to referrenced columns in main table by means of clustered index.the presence of an index will made searches in table quick by avoiding the query engine to scan through the whole table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-03 : 07:29:24

Then this is waht you want:-

select G1.A, G1.B, G1.C,G1.D,G2.D
from table1 G1
INNER JOIN (SELECT DISTINCT A,B,C,D FROM table2) G2
ON G1.A = G2.A
and G1.B = G2.B
and G1.C = G2.C

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-03 : 07:42:17

But you had told in your earlier post that you wanted all records from table1 and then matching records from table2 such that records from second table wont duplicate. Thats what i've done by rtaking DISTINCT records from second table and then matching on ur provided criteria. If you are still unclear or thinks this is not what you want. Can you provide some sample data from your tables and show what you expect output to be?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-03 : 08:09:34

ok. then this is what you want:-

select G1.A, G1.B, G1.C,G1.D,G2.D
from table1 G1
INNER JOIN (SELECT A,B,C,MAX(D)
FROM table2
GROUP BY A,B,C) G2
ON G1.A = G2.A
and G1.B = G2.B
and G1.C = G2.C

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-03 : 08:43:18

Ok. i see now. then you need something like this
if SQL 2005:-

SELECT G1.A, G1.B, G1.C,G1.D,G2.D
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY A, B, C ORDER BY D) AS RowNo,
A,B,C,D
FROM table1 
)G1
INNER JOIN 
(
SELECT ROW_NUMBER() OVER(PARTITION BY A, B, C ORDER BY D) AS RowNo,
A,B,C,D
FROM table2
)G2
ON G2.A = G1.A
and G2.B = G1.B
and G2.C = G1.C
AND G2.RowNo=G1.RowNo

If sql 2000:-

SELECT G1.A, G1.B, G1.C,G1.D,G2.D
FROM
(
SELECT (SELECT COUNT(*) + 1 
        FROM table1 
        WHERE A=t1.A 
        AND B=t1.B
        AND C=t1.C
        AND D < t1.D) AS RowNo,
A,B,C,D
FROM table1 t1
)G1
INNER JOIN 
(
SELECT (SELECT COUNT(*) + 1 
        FROM table2 
        WHERE A=t2.A 
        AND B=t2.B
        AND C=t2.C
        AND D < t2.D) AS RowNo,
A,B,C,D
FROM table2 t2
)G2
ON G2.A = G1.A
and G2.B = G1.B
and G2.C = G1.C
AND G2.RowNo=G1.RowNo

please make your requirement clear with some sample data in future for getting quick solutions. Cheers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources