swaggrK
asked on
SQL Join on field names
I need to join two SQL tables together.
"Table 1" contains the Question text for "Table 2"
"Table 2" contains my survey results.
My goal is to join the two tables together on their field names.
Example output:
Org_ID Organization_Name Consentor_a5
1 North Hospital contact@north.com
Table 1:
Table 2:
"Table 1" contains the Question text for "Table 2"
"Table 2" contains my survey results.
My goal is to join the two tables together on their field names.
Example output:
Org_ID Organization_Name Consentor_a5
1 North Hospital contact@north.com
Table 1:
SELECT [ORGANIZATION_NAME]
,[CONSENTOR_A5]
,[CONTACT_NAME]
,[TITLE]
,[EMAIL_ADDRESS]
,[PHONE_NUMBER]
,[GROUP]
,[DO_YOU_HAVE_CORE_PRIVILEGES]
,[CORE_PRIVILEGES_A16_M1]
,[CORE_PRIVILEGES_A16_M2]
,[CORE_PRIVILEGES_A17_M1]
,[CORE_PRIVILEGES_A17_M2]
,[CORE_PRIVILEGES_A18_M1]
,[CORE_PRIVILEGES_A18_M2]
,[CORE_PRIVILEGES_A4_M1]
,[CORE_PRIVILEGES_A4_M2]
,[CORE_PRIVILEGES_A5_M1]
,[CORE_PRIVILEGES_A5_M2]
,[CORE_PRIVILEGES_A6_M1]
,[CORE_PRIVILEGES_A6_M2]
,[CORE_PRIVILEGES_A7_M1]
,[CORE_PRIVILEGES_A7_M2]
,[CORE_PRIVILEGES_A9_M1]
,[CORE_PRIVILEGES_A9_M2]
,[CORE_PRIVILEGES_A10_M1]
,[CORE_PRIVILEGES_A10_M2]
,[CORE_PRIVILEGES_A8_M1]
,[CORE_PRIVILEGES_A8_M2]
,[CORE_PRIVILEGES_A11_M1]
,[CORE_PRIVILEGES_A11_M2]
,[CORE_PRIVILEGES_A13_M1]
,[CORE_PRIVILEGES_A13_M2]
,[CORE_PRIVILEGES_A14_M1]
,[CORE_PRIVILEGES_A14_M2]
,[CORE_PRIVILEGES_A2_M1]
,[CORE_PRIVILEGES_A2_M2]
,[CORE_PRIVILEGES_A19_M1]
,[CORE_PRIVILEGES_A19_M2]
FROM headers_core_priveleges
Table 2:
SELECT [ORG_ID]
,[ORGANIZATION_NAME]
,[CONSENTOR_A5]
,[CONTACT_NAME]
,[TITLE]
,[EMAIL_ADDRESS]
,[PHONE_NUMBER]
,[GROUP]
,[DO_YOU_HAVE_CORE_PRIVILEGES]
,[CORE_PRIVILEGES_A16_M1]
,[CORE_PRIVILEGES_A16_M2]
,[CORE_PRIVILEGES_A17_M1]
,[CORE_PRIVILEGES_A17_M2]
,[CORE_PRIVILEGES_A18_M1]
,[CORE_PRIVILEGES_A18_M2]
,[CORE_PRIVILEGES_A4_M1]
,[CORE_PRIVILEGES_A4_M2]
,[CORE_PRIVILEGES_A5_M1]
,[CORE_PRIVILEGES_A5_M2]
,[CORE_PRIVILEGES_A6_M1]
,[CORE_PRIVILEGES_A6_M2]
,[CORE_PRIVILEGES_A7_M1]
,[CORE_PRIVILEGES_A7_M2]
,[CORE_PRIVILEGES_A9_M1]
,[CORE_PRIVILEGES_A9_M2]
,[CORE_PRIVILEGES_A10_M1]
,[CORE_PRIVILEGES_A10_M2]
,[CORE_PRIVILEGES_A8_M1]
,[CORE_PRIVILEGES_A8_M2]
,[CORE_PRIVILEGES_A11_M1]
,[CORE_PRIVILEGES_A11_M2]
,[CORE_PRIVILEGES_A13_M1]
,[CORE_PRIVILEGES_A13_M2]
,[CORE_PRIVILEGES_A14_M1]
,[CORE_PRIVILEGES_A14_M2]
,[CORE_PRIVILEGES_A2_M1]
,[CORE_PRIVILEGES_A2_M2]
,[CORE_PRIVILEGES_A19_M1]
,[CORE_PRIVILEGES_A19_M2]
FROM core_priveleges
You will need an inner join. What field in each table has the same value to be joined on? With the assumption of Org_ID being the foreign key the following code should do what you need.
SELECT table1.Org_ID, table2.Organization_Name, table2.Consentor_a5
FROM table1
INNER JOIN table2
ON table1.Org_ID=table2.Org_ID
ASKER
Org_ID is only in "core_priveleges"
it is not in headers_core_priveleges
I need to join the tables on there field names. Both tables contain the same
field names. The only field that is not in both is org_id
it is not in headers_core_priveleges
I need to join the tables on there field names. Both tables contain the same
field names. The only field that is not in both is org_id
You cannot join on field names; you can only join on field values. How are you linking the question to the answer then? You should have a key field that is the same on all matching records in both tables.
Your question is not clear, but I am guessing you're looking for something like this:
If not please post sample data (from both tables) and expected result (what you want from the query)
declare @strSQL varchar(max)
set @strSQL = 'select ORG_ID, '
select @strSQL = @strSQL + '[ORGANIZATION_NAME] as [' + [ORGANIZATION_NAME] + '],',
' [CONSENTOR_A5] as [' + [CONSENTOR_A5] + '],',
-- ... and so on ...
' [CORE_PRIVILEGES_A19_M2] as [' + [CORE_PRIVILEGES_A19_M2] + ']'
FROM header_core_priveleges
set @strSQL = @strSQL + ' from core_privileges'
exec(@strSQL)
If not please post sample data (from both tables) and expected result (what you want from the query)
ASKER
@ ralmada I get the following error when I attempt to run
Error:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Error:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
ASKER
I have now created the following table that contains the questions(q_text), the matching field name(q_field) from the table "core_privelieves"
I am merely trying to join the question text from "q_core_priveleges" to the table that contains the survey results core_priveleges.
New Table 1:
SELECT q_id, q_field, q_text
FROM q_core_priveleges
I am merely trying to join the question text from "q_core_priveleges" to the table that contains the survey results core_priveleges.
New Table 1:
SELECT q_id, q_field, q_text
FROM q_core_priveleges
q_id q_field q_text
1 ORGANIZATION_NAME Organization Name
2 CONSENTOR_A5 Email Address
3 TITLE Title
Table 2 :
SELECT [ORG_ID]
,[ORGANIZATION_NAME]
,[CONSENTOR_A5]
,[CONTACT_NAME]
,[TITLE]
,[EMAIL_ADDRESS]
,[PHONE_NUMBER]
,[GROUP]
,[DO_YOU_HAVE_CORE_PRIVILEGES]
,[CORE_PRIVILEGES_A16_M1]
,[CORE_PRIVILEGES_A16_M2]
,[CORE_PRIVILEGES_A17_M1]
,[CORE_PRIVILEGES_A17_M2]
,[CORE_PRIVILEGES_A18_M1]
,[CORE_PRIVILEGES_A18_M2]
,[CORE_PRIVILEGES_A4_M1]
,[CORE_PRIVILEGES_A4_M2]
,[CORE_PRIVILEGES_A5_M1]
,[CORE_PRIVILEGES_A5_M2]
,[CORE_PRIVILEGES_A6_M1]
,[CORE_PRIVILEGES_A6_M2]
,[CORE_PRIVILEGES_A7_M1]
,[CORE_PRIVILEGES_A7_M2]
,[CORE_PRIVILEGES_A9_M1]
,[CORE_PRIVILEGES_A9_M2]
,[CORE_PRIVILEGES_A10_M1]
,[CORE_PRIVILEGES_A10_M2]
,[CORE_PRIVILEGES_A8_M1]
,[CORE_PRIVILEGES_A8_M2]
,[CORE_PRIVILEGES_A11_M1]
,[CORE_PRIVILEGES_A11_M2]
,[CORE_PRIVILEGES_A13_M1]
,[CORE_PRIVILEGES_A13_M2]
,[CORE_PRIVILEGES_A14_M1]
,[CORE_PRIVILEGES_A14_M2]
,[CORE_PRIVILEGES_A2_M1]
,[CORE_PRIVILEGES_A2_M2]
,[CORE_PRIVILEGES_A19_M1]
,[CORE_PRIVILEGES_A19_M2]
FROM core_priveleges
sorry the select should have been like this
select @strSQL = @strSQL + ' [ORGANIZATION_NAME] as [' + [ORGANIZATION_NAME] + '], ' +
' [CONSENTOR_A5] as [' + [CONSENTOR_A5] + '], ' +
-- --... and so on ...
' [CORE_PRIVILEGES_A19_M2] as [' + [CORE_PRIVILEGES_A19_M2] + ']'
FROM header_core_priveleges
I have now created the following table that contains the questions(q_text), the matching field name(q_field) from the table "core_privelieves"Still not clear, so I will keep guessing
select a.*, b.*
from core_privileges a
inner join (
SELECT q_id,
q_field,
q_text,
(select max(q_text) from q_core_privileges where q_field = 'ORGANIZATION_NAME') as [ORGANIZATION_NAME],
(select max(q_text) from q_core_privileges where q_field = 'CONSENTOR_A5') as [CONSENTOR_A5],
(select max(q_text) from q_core_privileges where q_field = 'TITLE') as [TITLE]
FROM q_core_priveleges
) b on a.ORGANIZATION_NAME = b.ORGANIZATION_NAME and a.[CONSENTOR_A5] = b.[CONSENTOR_A5] and a.TITLE = b.TITLE
If not, can you post a meaningful example with sample data from both tables and the expected result (not just the explanation but a resultset with what you expect to obtain)?
ASKER
@ ralmada, your example returned no results.
Below are both tables with values.
Table 1 contains the quesstion ID, question field name and question text.
Table 2 Contains the answers from 3 different organizations.
I need to create a report that shows the question text(qtext) along wiht the answers from Table 2.
So, I would like for the Question Text to output after the field name and before the results.
Table 1:
Table 2:
Below are both tables with values.
Table 1 contains the quesstion ID, question field name and question text.
Table 2 Contains the answers from 3 different organizations.
I need to create a report that shows the question text(qtext) along wiht the answers from Table 2.
So, I would like for the Question Text to output after the field name and before the results.
Table 1:
SELECT TOP qid, qfield, qtext
FROM q_core_privileges
qid qfield qtext
1 ORGANIZATION_NAME What is Your Organization Name
2 GROUP What is Your Group
3 DO_YOU_HAVE_CORE_PRIVILEGES Do You Have Core Privileges
Table 2:
SELECT ORG_ID, ORGANIZATION_NAME, [GROUP], DO_YOU_HAVE_CORE_PRIVILEGES
FROM core_privileges1
ORG_ID ORGANIZATION_NAME GROUP DO_YOU_HAVE_CORE_PRIVILEGES
1 Northwestern Hospital UHC Yes
2 Rush Medical Center UHC Yes
3 University of Chicago UHC Yes
OK, so now we have samples data from the two tables, but you're still missing the resultset
>>So, I would like for the Question Text to output after the field name and before the results.<<
Do you mean like this?
in that case try:
>>So, I would like for the Question Text to output after the field name and before the results.<<
Do you mean like this?
ORG_ID ORGANIZATION_NAME GROUP DO_YOU_HAVE_CORE_PRIVILEGES
What is Your Organization Name What is Your Group Do You Have Core Privileges
1 Northwestern Hospital UHC Yes
2 Rush Medical Center UHC Yes
3 University of Chicago UHC Yes
in that case try:
select '' as ORG_ID,
(select max(q_text) from q_core_privileges where q_field = 'ORGANIZATION_NAME') as [ORGANIZATION_NAME],
(select max(q_text) from q_core_privileges where q_field = 'CONSENTOR_A5') as [CONSENTOR_A5],
(select max(q_text) from q_core_privileges where q_field = 'TITLE') as [TITLE]
... and so on ...
union all
select * from core_privileges
or using PIVOT
select
*
from (
SELECT '' as ORG_ID, qfield, qtext
FROM q_core_privileges
) o
pivot(max(qtext) for qfield in ([ORGANIZATION_NAME]
,[CONSENTOR_A5]
,[CONTACT_NAME]
,[TITLE]
,[EMAIL_ADDRESS]
,[PHONE_NUMBER]
,[GROUP]
,[DO_YOU_HAVE_CORE_PRIVILEGES]
,[CORE_PRIVILEGES_A16_M1]
,[CORE_PRIVILEGES_A16_M2]
,[CORE_PRIVILEGES_A17_M1]
,[CORE_PRIVILEGES_A17_M2]
,[CORE_PRIVILEGES_A18_M1]
,[CORE_PRIVILEGES_A18_M2]
,[CORE_PRIVILEGES_A4_M1]
,[CORE_PRIVILEGES_A4_M2]
,[CORE_PRIVILEGES_A5_M1]
,[CORE_PRIVILEGES_A5_M2]
,[CORE_PRIVILEGES_A6_M1]
,[CORE_PRIVILEGES_A6_M2]
,[CORE_PRIVILEGES_A7_M1]
,[CORE_PRIVILEGES_A7_M2]
,[CORE_PRIVILEGES_A9_M1]
,[CORE_PRIVILEGES_A9_M2]
,[CORE_PRIVILEGES_A10_M1]
,[CORE_PRIVILEGES_A10_M2]
,[CORE_PRIVILEGES_A8_M1]
,[CORE_PRIVILEGES_A8_M2]
,[CORE_PRIVILEGES_A11_M1]
,[CORE_PRIVILEGES_A11_M2]
,[CORE_PRIVILEGES_A13_M1]
,[CORE_PRIVILEGES_A13_M2]
,[CORE_PRIVILEGES_A14_M1]
,[CORE_PRIVILEGES_A14_M2]
,[CORE_PRIVILEGES_A2_M1]
,[CORE_PRIVILEGES_A2_M2]
,[CORE_PRIVILEGES_A19_M1]
,[CORE_PRIVILEGES_A19_M2]
)) p
union all
select * from core_privileges
ASKER
Thanks ralmada but I keep getting the following error with both queries...
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
that's because you need to have the same # columns in both sides of the UNION. You've mentioned that both tables were the same except for the ORG_ID column, which I've manually added in the first part of the UNION ALL.
Run each query on both side of the UNION separately, and make sure you have the same # of columns. Once the columns match, you can combine them in the UNION ALL
Run each query on both side of the UNION separately, and make sure you have the same # of columns. Once the columns match, you can combine them in the UNION ALL
ASKER
Thanks ralmada, the following worked, but I had to create a table that did not contain org_ID.
But, this will not be the case, I will need the org_ID.
How can I use my core_privileges table that does contain org_ID?
But, this will not be the case, I will need the org_ID.
How can I use my core_privileges table that does contain org_ID?
select
[ORGANIZATION_NAME]
,[GROUP]
,[DO_YOU_HAVE_CORE_PRIVILEGES]
,[CORE_PRIVILEGES_A16_M1]
,[CORE_PRIVILEGES_A16_M2]
,[CORE_PRIVILEGES_A17_M1]
,[CORE_PRIVILEGES_A17_M2]
,[CORE_PRIVILEGES_A18_M1]
,[CORE_PRIVILEGES_A18_M2]
,[CORE_PRIVILEGES_A4_M1]
,[CORE_PRIVILEGES_A4_M2]
,[CORE_PRIVILEGES_A5_M1]
,[CORE_PRIVILEGES_A5_M2]
,[CORE_PRIVILEGES_A6_M1]
,[CORE_PRIVILEGES_A6_M2]
,[CORE_PRIVILEGES_A7_M1]
,[CORE_PRIVILEGES_A7_M2]
,[CORE_PRIVILEGES_A9_M1]
,[CORE_PRIVILEGES_A9_M2]
,[CORE_PRIVILEGES_A10_M1]
,[CORE_PRIVILEGES_A10_M2]
,[CORE_PRIVILEGES_A8_M1]
,[CORE_PRIVILEGES_A8_M2]
,[CORE_PRIVILEGES_A11_M1]
,[CORE_PRIVILEGES_A11_M2]
,[CORE_PRIVILEGES_A13_M1]
,[CORE_PRIVILEGES_A13_M2]
,[CORE_PRIVILEGES_A14_M1]
,[CORE_PRIVILEGES_A14_M2]
,[CORE_PRIVILEGES_A2_M1]
,[CORE_PRIVILEGES_A2_M2]
,[CORE_PRIVILEGES_A19_M1]
,[CORE_PRIVILEGES_A19_M2]
from (
SELECT '' as ORG_ID, qfield, qtext
FROM q_core_privileges
) o
pivot(max(qtext) for qfield in ([ORGANIZATION_NAME]
,[GROUP]
,[DO_YOU_HAVE_CORE_PRIVILEGES]
,[CORE_PRIVILEGES_A16_M1]
,[CORE_PRIVILEGES_A16_M2]
,[CORE_PRIVILEGES_A17_M1]
,[CORE_PRIVILEGES_A17_M2]
,[CORE_PRIVILEGES_A18_M1]
,[CORE_PRIVILEGES_A18_M2]
,[CORE_PRIVILEGES_A4_M1]
,[CORE_PRIVILEGES_A4_M2]
,[CORE_PRIVILEGES_A5_M1]
,[CORE_PRIVILEGES_A5_M2]
,[CORE_PRIVILEGES_A6_M1]
,[CORE_PRIVILEGES_A6_M2]
,[CORE_PRIVILEGES_A7_M1]
,[CORE_PRIVILEGES_A7_M2]
,[CORE_PRIVILEGES_A9_M1]
,[CORE_PRIVILEGES_A9_M2]
,[CORE_PRIVILEGES_A10_M1]
,[CORE_PRIVILEGES_A10_M2]
,[CORE_PRIVILEGES_A8_M1]
,[CORE_PRIVILEGES_A8_M2]
,[CORE_PRIVILEGES_A11_M1]
,[CORE_PRIVILEGES_A11_M2]
,[CORE_PRIVILEGES_A13_M1]
,[CORE_PRIVILEGES_A13_M2]
,[CORE_PRIVILEGES_A14_M1]
,[CORE_PRIVILEGES_A14_M2]
,[CORE_PRIVILEGES_A2_M1]
,[CORE_PRIVILEGES_A2_M2]
,[CORE_PRIVILEGES_A19_M1]
,[CORE_PRIVILEGES_A19_M2]
)) p
union all
select * from core_privileges2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks ralmada! This works just fine.
ASKER
Example
Organization_Name Consentor_A5
Organization Name Email Address
North Hospital contact@north.com