Link to home
Start Free TrialLog in
Avatar of lrr81765
lrr81765

asked on

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??
Avatar of rafrancisco
rafrancisco

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]
ASKER CERTIFIED SOLUTION
Avatar of rafrancisco
rafrancisco

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial