SQL Server: join query

Question

I have 3 tables (forms, fields, fieldsperform):

forms   |   fields   |   fieldsperform
  id    |      id    |          formid
 group  |    group   |        fieldid
 name   |    name

What I want is to add in fieldsperform the same forms-fields couples but for another group. This is what I have now:

Forms:

id   |  group  |  name
 0   |  groupA |  formA
 1   |  groupA |  formB
 2   |  groupB |  formA
 3   |  groupB |  formB

Fields

id  |  group  |  name
0   |  groupA | fieldA
1   |  groupA | fieldB
2   |  groupA | fieldC
3   |  groupB | fieldA
4   |  groupB | fieldB
5   |  groupB | fieldC

FieldsPerForm

formid   |   fieldid
  0      |      0       (formA - fieldA)
  0      |      2       (formA - fieldC)
  1      |      1       (formB - fieldB)
  1      |      2       (formB - fieldC)

And I want to insert the same couples but for the groupB. So I want to insert :

formid   |   fieldid
  2      |      3       (formA - fieldA)
  2      |      5       (formA - fieldC)
  3      |      4       (formB - fieldB)
  3      |      5       (formB - fieldC)

Logic:

Check the couples in FieldsPerForm. The first couple is (0,0). formid = 0 is formA in the Forms table. In the Forms table the formA form for groupB has id = 2 so the formid of the first couple is 2. Then do the same for the Fields table and so on

Your logic for forming group B pairs is not clear to me. Why do we not have (2, 4) as a group B pair? — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 9, 2017 at 10:24
@TimBiegeleisen (2,4) pair for group B is (formA,fieldB), this pair doesnt exist for Group A . — aggicd
– aggicd, Commented Oct 9, 2017 at 10:26
Please edit your question with the exact logic for forming pairs in both groups. There is no sort of when writing a query; if you can't give logic, then you can't query it. — Tim Biegeleisen
– Tim Biegeleisen, Commented Oct 9, 2017 at 10:31

deependrax · Accepted Answer · 2017-10-09 11:08:35Z

1

INSERT INTO FieldsPerForm(formid, fieldid)
SELECT fo.id, fi.id from 
(SELECT fo.name as foname, fi.name as finame
    FROM FieldsPerForm fpf 
    JOIN Forms fo ON (fpf.formid = fo.id)
    JOIN Fields fi ON (fpf.fieldid = fi.id)) datatbl 
JOIN forms fo ON (fo.name = datatbl.foname)
JOIN fields fi ON (fi.name = datatbl.foname)
WHERE datatbl.group = 'groupA' AND fo.group = 'groupB' 
and fi.group = 'groupB';

edited Oct 9, 2017 at 11:08

answered Oct 9, 2017 at 10:21

deependrax

567 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

aggicd Over a year ago

Yes, the same couples but I want the ids for groupB

Serkan Arslan · Accepted Answer · 2017-10-09 11:58:29Z

You can use this query

DECLARE @Forms AS TABLE(id INT, [group]  VARCHAR(10),  name VARCHAR(10))
INSERT INTO @Forms
VALUES
('0','groupA','formA'),
('1','groupA','formB'),
('2','groupB','formA'),
('3','groupB','formB')

DECLARE @Fields AS TABLE(id INT, [group]  VARCHAR(10),  name VARCHAR(10))
INSERT INTO @Fields
VALUES
('0','groupA','fieldA'),
('1','groupA','fieldB'),
('2','groupA','fieldC'),
('3','groupB','fieldA'),
('4','groupB','fieldB'),
('5','groupB','fieldC')

DECLARE @FieldsPerForm TABLE(formid INT,   fieldid INT)
INSERT INTO @FieldsPerForm 
VALUES
(0,0), --  (formA - fieldA)
(0,2), --  (formA - fieldC)
(1,1), --  (formB - fieldB)
(1,2)  --  (formB - fieldC)

INSERT INTO @FieldsPerForm
SELECT FRM_B.id , FLD_B.id 
FROM @Forms FRM_A 
    INNER JOIN @Fields FLD_A ON FRM_A.[group] = FLD_A.[group] 
    INNER JOIN @FieldsPerForm FF ON FF.fieldid = FLD_A.id AND FF.formid = FRM_A.id
    INNER JOIN @Forms FRM_B ON FRM_A.name = FRM_B.name AND FRM_B.[group] = 'groupB'
    INNER JOIN @Fields FLD_B ON FLD_A.name = FLD_B.name AND FLD_B.[group] = 'groupB'
WHERE 
    FRM_A.[group] ='groupA'
ORDER BY 
    FF.formid, FF.fieldid

Result:

id          id
----------- -----------
2           3
2           5
3           4
3           5

Chris Mack · Accepted Answer · 2017-10-09 10:40:43Z

0

INSERT INTO FieldsPerForm
(
    formid
    , fieldid
)

SELECT
    FO.id
    , FI.id
FROM
    Forms FO
    JOIN Fields FI ON
        FO.[group] = FI.[group]
        AND
            (
                (FO.name = 'formA' AND FI.name = 'fieldA')
                OR (FO.name = 'formA' AND FI.name = 'fieldC')
                OR (FO.name = 'formB' AND FI.name = 'fieldB')
                OR (FO.name = 'formB' AND FI.name = 'fieldC')
            )
WHERE FO.[group] = 'groupB' -- optional WHERE clause

edited Oct 9, 2017 at 10:40

answered Oct 9, 2017 at 10:25

Chris Mack

5,2082 gold badges16 silver badges31 bronze badges

8 Comments

aggicd Over a year ago

I am not looking for a solution with static names. There are a lot of fields and forms so is not going to work

Chris Mack Over a year ago

"formA has 2 fields: fieldA and fieldC" - this isn't clear from the data. If you removed the WHERE clause, or altered it to <> 'groupA', it would replicate the current pattern for as many groups as you have.

aggicd Over a year ago

It is clear. In the FieldsPerForm there are only 2 pairs for formA : formA - fieldA and formA - fieldC

Chris Mack Over a year ago

If the logic needs to come from the FieldsPerForm table, I don't think you can get a simpler solution than the one I've provided above. There are 4 combinations, and there's no logic to these combinations, other than the fact that they already exist in the table.

aggicd Over a year ago

Thats true. I just want to copy the existing. You can see the algorithm I want in my edited post

|

Chri Schi · Accepted Answer · 2017-10-09 12:00:17Z

Try this one (you can use the code as a stored procedure, MSSQL):

 DECLARE @SourceGroup varchar(max), @DestinationGroup varchar(max)

 SET @SourceGroup = 'groupA'
 SET @DestinationGroup = 'groupB'

INSERT INTO FieldsPerForm (FormID, FieldID)

SELECT  FO1.ID as FormIDNew,
        FI1.ID as FieldIDNew
FROM dbo.FieldsPerForm FPF 
    JOIN 
        dbo.Forms FO ON FPF.FormID = FO.ID and FO.[Group] = @SourceGroup
    JOIN
        dbo.Fields FI ON FPF.FieldID = FI.ID and FI.[Group] = @SourceGroup
    JOIN 
        dbo.Forms FO1 ON FO1.Name = FO.Name and FO1.[Group] = @DestinationGroup and FO.[Group] = @SourceGroup
    JOIN 
        dbo.Fields FI1 ON FI1.Name = FI.Name and FI1.[Group] = @DestinationGroup and FI.[Group] = @SourceGroup

Collectives™ on Stack Overflow

SQL Server: join query

4 Answers 4

1 Comment

Comments

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related