• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

Distinct row from Left join

Ok, I am trying to return the first row of the left side of the join. Here are some tables and sample data:

CREATE TABLE [dbo].[FieldUniqueId] (
      [FormUniqueId] [bigint] IDENTITY (1, 1) NOT NULL ,
      [FieldId] [int] NULL
) ON [PRIMARY]
GO

Sample Data
-----------
1001      4200
1002      4200
1003      4200


CREATE TABLE [dbo].[FormFields] (
      [FieldID] [int] IDENTITY (1, 1) NOT NULL ,,
      [Name] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Sample Data
--------------------
4200      Test Data


Create Table #FF (

      FieldName varchar(200),
      Response varchar(1000))

Sample Data
--------------
Test Data            This is test one
      



What I am Getting now
--------------------------------------------------------
FormFields.FieldID      FieldUniqueId.FormUniqueId      #FF.FieldName      #FF.Response
4200                        1001                                    Test Data            This is test one
4200                        1002                                    Test Data            This is test one
4200                        1003                                    Test Data            This is test one

Current Query
-----------------------------------------------------
select  FU.FormUniqueId,FF.FieldId,t.Response,t.Score into #ids
from FormFields FF
inner join FieldUniqueId FU on FF.FieldId  = FU.FieldId
inner join #FF t on t.[FieldName] = FF.[Name]


Desired Output
---------------------------------------------------------
FormFields.FieldID      FieldUniqueId.FormUniqueId      #FF.FieldName      #FF.Response
4200                        1001                                    Test Data            This is test one

Can you fix my query??
0
lrr81765
Asked:
lrr81765
  • 2
1 Solution
 
rafranciscoCommented:
If you just want the first row, you can try this:

select  TOP 1 FU.FormUniqueId,FF.FieldId,t.Response,t.Score into #ids
from FormFields FF
inner join FieldUniqueId FU on FF.FieldId  = FU.FieldId
inner join #FF t on t.[FieldName] = FF.[Name]
0
 
rafranciscoCommented:
But if you want the the lowest FormUniqueID for each FieldID, you try this:

select  FU.FormUniqueId,FF.FieldId,t.Response,t.Score into #ids
from FormFields FF
inner join FieldUniqueId FU on FF.FieldId  = FU.FieldId
inner join #FF t on t.[FieldName] = FF.[Name]
WHERE FU.FormUniqueID = (SELECT MIN(FormUniqueID) FROM FiedUniqueId FU2
WHERE FU.FieldID = FU2.FieldID)
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now