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.Respons e,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??
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_
) 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
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
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
4200 1001 Test Data This is test one
Can you fix my query??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select TOP 1 FU.FormUniqueId,FF.FieldId
from FormFields FF
inner join FieldUniqueId FU on FF.FieldId = FU.FieldId
inner join #FF t on t.[FieldName] = FF.[Name]