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

Data Selection Problem

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
1004     4201
1005     4201

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
4201     TypeBusiness

CREATE TABLE [FormFieldOptions] (
      [FieldID] [int] NULL ,
      [OptionValue] [varchar] )

Sample Data
-------------------------------------------
4201      Partnership
4201      Corporation

Create Table #FF (

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

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

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]
WHERE FU.FormUniqueID = (SELECT MIN(FormUniqueID) FROM FieldUniqueId FU2
WHERE FU.FieldID = FU2.FieldID)


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

---
The basic rule is if the FF.FieldName exists in the FormFieldOptions, find the FieldUniqueId.FormUniqueId of the FormFieldOptions that matches the FF.Response. Boy, this is giving me a headache.

Can you fix my query??
0
lrr81765
Asked:
lrr81765
  • 3
  • 2
1 Solution
 
obahatCommented:
SELECT fui.FormUniqueId
FROM #FF ff
      INNER JOIN FormFields ffi
      ON ff.FieldName = ffi.Name
      INNER JOIN FormFieldOptions ffo
      ON ffo.FieldID = ffi.FieldID
      INNER JOIN FieldUniqueId fui
      ON fui.FieldId = ffo.FieldId

This returns all the matching FormUniqueId values. If you only need one value, just do a group by.
This is not very clear from your question, hence I only provide the basic query.
0
 
BillAn1Commented:
the only thing I can see wrong with your query, based on the same data you provide is that you didn;t include the #FF.FieldName in your select statement, instead you have a "Score" field which doesnt exist in your table definition.
(plus you get the cols in a different order)

this SQL give the reuslt below :
select  FF.FieldId,FU.FormUniqueId,t.FieldName,t.Response
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 FieldUniqueId FU2
WHERE FU.FieldID = FU2.FieldID)

FieldId     FormUniqueId   FieldName   Response  
4200        1001                 Test Data         This is test one
4201        1004                 TypeBusiness    Partnership

which looks to me like what you want?

Also, there are a few syntax errors in your table definitions. I assume these are not your actual tables / actual SQL you are running.


0
 
lrr81765Author Commented:
Thanks for the quick responses.
This is "trimmed" code for brevity.  Here are some changed tables - sorry for the confusion.
BillAn1 - you result was close, but it only worked because the Partnership was the MIN(). If I selected Corporation, it wouldn't return 1005. This is probably because I didn't post the right tables.

So psuedo code :
             if t.Response exists in FormFieldOptions where FieldId=FF.FieldId then
                    formUniqueId = 1005 ( see below)
             else 'does not exist in options table
                   formUniqueId = 1001

Hope this is clearer.

CREATE TABLE [dbo].[FormFieldOptions] (
      [FieldID] [int] NULL ,
      [FormFieldOptionsID] [varchar] (128)  ,
      [OptionValue] [varchar] (128)
) ON [PRIMARY]

Sample Data
4201     213  Partnership        
4201     214  Corporation

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

Sample Data
-----------
1001     4200    NULL
1002     4200    NULL
1003     4200    NULL
1004     4201    213 -->matches TypeBusiness = Partnership
1005     4201    214 -->mathes  Typebusiness=Corporation


0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
BillAn1Commented:
I'm sorry I don't understand you. perhaps you can explain again.
<<If I selected Corporation, it wouldn't return 1005>> I don't know what you mean by this at all? What / where / how are you selecting, and what/where/how  are you returning?

<<             if t.Response exists in FormFieldOptions where FieldId=FF.FieldId then
                    formUniqueId = 1005 ( see below)
             else 'does not exist in options table
                   formUniqueId = 1001
>>

this does not mean anything to me.

I notice you have added a new table, and a new column. Can you give an explanation of how these relate to each other?
0
 
lrr81765Author Commented:
Ok, just to enlighten, these are for storing answers of HTML forms. Each element is given a unique id (FieldUniqueId). Fields (i.e. INPUT ) exist in the FormFields table and if it is a option type field (i.e. RADIO) an entry exists in the form fields, and then all the other options exist in the FormFieldOptions table.

When I go to get the responses, I match a FieldUniqueId to the item and store the response.

So, from the form I get the Name of the field and the value (i.e. TypeBusiness & Partnership) and I have to match it up with the uniqueid in the table (1005 in this example).

I get 1005 by first matching the fieldid (TypeBusiness) to the FormFieldOptions table, which gives me the FieldId. I can get the FormFieldOption.FormFieldOptionsID by matching the field name  & option value.

The real problem comes in that the field exists in the FormFields table AND the FormFieldOptions table (don't blame me, just trying to fix someone else's work).

So if the value does not exist in the FormFieldOptions table, I need the id for the FormFields.

I hope this sheds some light. Thanks for your help.
0
 
BillAn1Commented:

OK I think I understand your system (although it doesn't make any sense to me as a database design :-))
Each field on the form has a single record in FormFields.
I don't undertstand why there are multiple identical rows in the FieldUniqueID table for the one field 4200, which has no options at all?
That aside what I think you want is as follows :
Join FormFields to FieldUniqueID then do a LEFT OUTER join to FormFiledOptions.
Then you can join this to your selection - if the option value exists in FormFieldOptions then the Respons must match this.

select FF.FieldID, min(FormUniqueID), FF.Name, #ff.response
from FormFields FF join FieldUniqueId FUI on FF.FieldID = FUI.fieldID
left outer join FormFieldOptions FFO
on fui.fieldID = ffo.fieldID and fui.optionID = ffo.formfieldoptionsID
join #FF on FF.name = #ff.FieldName and (ffo.OptionValue is NULL or ffo.OptionValue = #ff.Response)
group by FF.FieldID, FF.Name, #ff.response
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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