GIStewart
asked on
Modifying a query to account for spaces in field names
I have a query borrowed from Jeff Smith for comparing 2 tables in SQL server, and I'm now trying to adapt it for field names in a table (that I don't control) which have spaces. For example, let's say COL1 below was actually COL 1. I have tried square brackets, but that doesn't seem to work. Thank you.
SELECT Min(tmp.TableName) AS TableName, tmp.ID, tmp.COL1, tmp.COL2, tmp.COL3
FROM [SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3
FROM TblA as A
UNION ALL
SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3
FROM TblB as B
]. AS tmp
GROUP BY tmp.ID, tmp.COL1, tmp.COL2, tmp.COL3
HAVING (((Count(*))=1))
ORDER BY tmp.ID;
SELECT Min(tmp.TableName) AS TableName, tmp.ID, tmp.COL1, tmp.COL2, tmp.COL3
FROM [SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3
FROM TblA as A
UNION ALL
SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3
FROM TblB as B
]. AS tmp
GROUP BY tmp.ID, tmp.COL1, tmp.COL2, tmp.COL3
HAVING (((Count(*))=1))
ORDER BY tmp.ID;
you have to change the "[" to "(" on this part of the query
FROM [SELECT to FROM (SELECT
you have to change the "]." to ")" on this part
]. AS tmp to ) AS tmp
so your query will look like this
SELECT Min(tmp.TableName) AS TableName, tmp.ID, tmp.COL1, tmp.COL2, tmp.COL3
FROM (SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3
FROM TblA as A
UNION ALL
SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3
FROM TblB as B
) AS tmp
GROUP BY tmp.ID, tmp.COL1, tmp.COL2, tmp.COL3
HAVING (((Count(*))=1))
ORDER BY tmp.ID;
also, enclose fields with spaces in [ ] like [COL 1]
FROM [SELECT to FROM (SELECT
you have to change the "]." to ")" on this part
]. AS tmp to ) AS tmp
so your query will look like this
SELECT Min(tmp.TableName) AS TableName, tmp.ID, tmp.COL1, tmp.COL2, tmp.COL3
FROM (SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3
FROM TblA as A
UNION ALL
SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3
FROM TblB as B
) AS tmp
GROUP BY tmp.ID, tmp.COL1, tmp.COL2, tmp.COL3
HAVING (((Count(*))=1))
ORDER BY tmp.ID;
also, enclose fields with spaces in [ ] like [COL 1]
ASKER
Neither option appeared to work. I am trying to do this in access, and I have attached my sample db.
Query1 compares TblA and TblB, and seems to work fine.
Query2 compares TblC and TblD, and has the square brackets. (TblC is the same as TblA, TblD is the same as TblB). But when I try and run that query, I get an "Invalid bracketing of name....." message, without even adding any spaces in the field names. I'm not sure if it's another bracketing issue as capricorn1 suggests. Thank you.
EE-Compare-test.mdb
Query1 compares TblA and TblB, and seems to work fine.
Query2 compares TblC and TblD, and has the square brackets. (TblC is the same as TblA, TblD is the same as TblB). But when I try and run that query, I get an "Invalid bracketing of name....." message, without even adding any spaces in the field names. I'm not sure if it's another bracketing issue as capricorn1 suggests. Thank you.
EE-Compare-test.mdb
this is the revision of your query "query2"
SELECT Min(tmp.TableName) AS TableName, (tmp.ID), (tmp.COL1), (tmp.COL2), (tmp.COL3)
FROM (SELECT 'Table C' as TableName, (C.ID), (C.COL1), (C.COL2), (C.COL3)
FROM TblC as C
UNION ALL SELECT 'Table D' as TableName, (D.ID), (D.COL1), (D.COL2), (D.COL3)
FROM TblD as D
) AS tmp
GROUP BY (tmp.ID), (tmp.COL1), (tmp.COL2), (tmp.COL3)
HAVING (((Count(*))=1))
ORDER BY (tmp.ID);
see query3
EE-Compare-test.mdb
SELECT Min(tmp.TableName) AS TableName, (tmp.ID), (tmp.COL1), (tmp.COL2), (tmp.COL3)
FROM (SELECT 'Table C' as TableName, (C.ID), (C.COL1), (C.COL2), (C.COL3)
FROM TblC as C
UNION ALL SELECT 'Table D' as TableName, (D.ID), (D.COL1), (D.COL2), (D.COL3)
FROM TblD as D
) AS tmp
GROUP BY (tmp.ID), (tmp.COL1), (tmp.COL2), (tmp.COL3)
HAVING (((Count(*))=1))
ORDER BY (tmp.ID);
see query3
EE-Compare-test.mdb
ASKER
thank you - so if Col1 was really Col 1 (i.e. with a space), how would I modify Query3?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
this works perfectly, thank you.
This should work:
SELECT Min(tmp.TableName) AS TableName, [tmp.ID], [tmp.COL 1], [tmp.COL 2], [tmp.COL 3]
...