CODEWITHSUNDEEP
sqlsql-server
Coding Duchess
Coding Duchess

Reputation: 6919

Is there a way to join tables on only the first occurrence of the record

I am joining two tables, or rather a query and a sub-query on multiple fields. So For each PK in the first query I can get one or more records from the second sub-query. Is there a way to return only TOP 1 match from both queries?

Example:

SELECT t1.ID, a.FieldID, a.Field1, a.Field2, a.Field3, a.Field4
FROM Table1 as t1
INNER JOIN 
(SELECT t2.FieldID, t2.Field1, t2.Field2, t3.Field3, t3.Field4 FROM Table2 as t2
INNER JOIN Table3 as t3 ON t2.ID=t3.IDFrom2) as a
ON t1.IDFor2=a.FieldID
WHERE LEN(a.[Field1]) < 80 AND LEN(a.Field3) < 60
AND (CHARINDEX(LTRIM(RTRIM(REPLACE(t1.Field1,' ',''))),LTRIM(RTRIM(REPLACE(a.Field1,' ',''))))>0 OR CHARINDEX(LTRIM(RTRIM(REPLACE(a.Field1,' ',''))),LTRIM(RTRIM(REPLACE(t1.Field1,' ',''))))>0 OR t1.Field1 IS NULL)
AND (CHARINDEX(LTRIM(RTRIM(t1.Field2)),LTRIM(RTRIM(a.Field2)))>0 or CHARINDEX(LTRIM(RTRIM(a.Field2)),LTRIM(RTRIM(t1.Field2)))>0 OR t1.Field2 IS NULL)
AND (CHARINDEX(LTRIM(RTRIM(t1.Field3)),LTRIM(RTRIM(a.Field3)))>0 OR t1.Field3 IS NULL)
AND CHARINDEX(LTRIM(RTRIM(t1.Field4)),LTRIM(RTRIM(a.Field4)))>0

Subquery

SELECT t2.FieldID, t2.Field1, t2.Field2, t3.Field3, t3.Field4 FROM Table2 as t2
INNER JOIN Table3 as t3 ON t2.ID=t3.IDFrom2

returns a number of records (that have the same t2.Field) that have relationship with Table1 when ran separately, just filtering t2.FieldID equals to a particular IDFor2 from Table1

But when the two queries are joined together and matched on various fields, I get twice as many records because it returns some duplicates for each t1.ID. How can I modify my query in order to retrieve only one record (TOP 1) per each t1.ID?

P.S. Unfortunately I cannot provide real data as it is confidential.

UPDATE:

the sub-query

SELECT t2.FieldID, t2.Field1, t2.Field2, t3.Field3, t3.Field4 FROM Table2 as t2
INNER JOIN Table3 as t3 ON t2.ID=t3.IDFrom2

returns two tables below combined:

Table2                                   Table3
_____________________________________     _________________
abc123    Company1   Industry1  def       def   300   0.67
abc123    Company1   Industry1  fgh       fgh   500   0.81
abc123    Company1   Industry1  hjk       hjk   80    0.0045
abc123    Company2   Industry2  lmn       lmn   250   0.003
abc123    NULL       NULL       vck       vck   500   0.81


Table1
______________________________________________________
10001   abc123  1   Comp       Industry1   300   0.67
10002   abc123  2   NULL       NULL        500   0.81
10003   abc123  3   NULL       NULL        80    0.0045
10004   abc123  4   Company2   Industr     250   0.003
10005   abc123  5   NULL       NULL        500   0.81

I need to combine Table2 and Table3 data on keys which are def, fgh etc...

Then I need to match Table1 to the merged Table2 and Table3 and sort of "enrich" Table1 data with whatever gets pulled out of Tables 2 and 3. I want a result of something like:

10001   abc123    Company1       Industry1   300   0.67
10002   abc123    Company1       Industry1   500   0.81
10003   abc123    Company1       Industry1   80    0.0045
10004   abc123    Company2       Industry2   250   0.003
10005   abc123    Company2       Industry2   500   0.81

instead I get sort of a cartesian product:

10001   abc123  1   Company1    Industry1   300   0.67
10002   abc123  2   Company1    Industry1       500   0.81
10002   abc123  2   Company1    Industry2       500   0.81
10003   abc123  3   Company1    Industry1       80    0.0045
10004   abc123  4   Company2    Industry2       250   0.003
10005   abc123  5   Company2    Industry2       500   0.81
10005   abc123  5   Company2    Industry1       500   0.81

UPDATE 2:

I used Ionic's approach and it worked. I modified my query to use row_number and partition:

SELECT * FROM
(SELECT t1.ID, a.FieldID, a.Field1, a.Field2, a.Field3, a.Field4
FROM Table1 as t1
INNER JOIN 
(SELECT t2.FieldID, t2.Field1, t2.Field2, t3.Field3, t3.Field4 FROM Table2 as t2
INNER JOIN Table3 as t3 ON t2.ID=t3.IDFrom2) as a
ON t1.IDFor2=a.FieldID
WHERE LEN(a.[Field1]) < 80 AND LEN(a.Field3) < 60
AND (CHARINDEX(LTRIM(RTRIM(REPLACE(t1.Field1,' ',''))),LTRIM(RTRIM(REPLACE(a.Field1,' ',''))))>0 OR CHARINDEX(LTRIM(RTRIM(REPLACE(a.Field1,' ',''))),LTRIM(RTRIM(REPLACE(t1.Field1,' ',''))))>0 OR t1.Field1 IS NULL)
AND (CHARINDEX(LTRIM(RTRIM(t1.Field2)),LTRIM(RTRIM(a.Field2)))>0 or CHARINDEX(LTRIM(RTRIM(a.Field2)),LTRIM(RTRIM(t1.Field2)))>0 OR t1.Field2 IS NULL)
AND (CHARINDEX(LTRIM(RTRIM(t1.Field3)),LTRIM(RTRIM(a.Field3)))>0 OR t1.Field3 IS NULL)
AND CHARINDEX(LTRIM(RTRIM(t1.Field4)),LTRIM(RTRIM(a.Field4)))>0) as b
WHERE b.rn=1

Upvotes: 4

Views: 9577

Answers (2)

Ravi Teja
Ravi Teja

Reputation: 55

You can do it with the help of CTE function. Where you can create the select statement as you wish inside the CTE and then joining that CTE with the main table to get the results that you like

    WITH A
    (
      SELECT t2.id, t2.name, t2.create_date, 
        ROW_NUMBER() OVER(PARTITION BY t2.id ORDER BY t2.create_date) as rn
    FROM t2
    )
   SELECT t1.ID, a.FieldID, a.Field1, a.Field2, a.Field3, a.Field4 FROM A
     INNER JOIN T1
     ON t1.id = t2.id
   WHERE A.RN = 1

Upvotes: 0

Ionic
Ionic

Reputation: 3935

Well you can run it on a subquery and run it for every t1.ID to retrieve the first value back. But on the other hand you can try to solve this using ROW_NUMBER().

Here is a small example, given you have two tables t1 and t2. You want just the first occurrence in t2 back.

SELECT *
FROM t1
INNER JOIN (
        SELECT t2.id, t2.name, t2.create_date, 
            ROW_NUMBER() OVER(PARTITION BY t2.id ORDER BY t2.create_date) as rn
        FROM t2
    ) as t2
    ON t1.id = t2.id
WHERE t2.rn = 1

This will number all rows in this case based on the create_date (in your case your logic behind the sentence "the first occurrence") and partition (group) based on the t2.id.

Afterwards you join it and only take care of the rows where t2.rn = 1

Upvotes: 5

Related Questions