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

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;
0
GIStewart
Asked:
GIStewart
  • 3
  • 3
1 Solution
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Did you try adding the brackets to the field names or a whole query?

This should work:

SELECT Min(tmp.TableName) AS TableName, [tmp.ID], [tmp.COL 1], [tmp.COL 2], [tmp.COL 3]
...
0
 
Rey Obrero (Capricorn1)Commented:
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]
0
 
GIStewartAuthor Commented:
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Rey Obrero (Capricorn1)Commented:
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
0
 
GIStewartAuthor Commented:
thank you - so if Col1 was really Col 1 (i.e. with a space), how would I modify Query3?
0
 
Rey Obrero (Capricorn1)Commented:
if COL1 was really Col 1
you will enclose it in brackets, like this [COL 1]

this will be the format of the query

SELECT Min(tmp.TableName) AS TableName, (tmp.ID), (tmp.[COL 1]), (tmp.COL2), (tmp.COL3)
FROM (SELECT 'Table C' as TableName, (C.ID), (C.[COL 1]), (C.COL2), (C.COL3)
  FROM TblC as C
  UNION ALL SELECT 'Table D' as TableName, (D.ID), (D.[COL 1]), (D.COL2), (D.COL3)
  FROM TblD as D
) AS tmp
GROUP BY (tmp.ID), (tmp.[COL 1]), (tmp.COL2), (tmp.COL3)
HAVING (((Count(*))=1))
ORDER BY (tmp.ID);


or this, removing the unnecessary "("   and  ")"



SELECT Min(tmp.TableName) AS TableName, tmp.ID, tmp.[COL 1], tmp.COL2, tmp.COL3
FROM (SELECT 'Table C' as TableName, C.ID, C.[COL 1], C.COL2, C.COL3
  FROM TblC as C
  UNION ALL SELECT 'Table D' as TableName, D.ID, D.[COL 1], D.COL2, D.COL3
  FROM TblD as D
) AS tmp
GROUP BY tmp.ID, tmp.[COL 1], tmp.COL2, tmp.COL3
HAVING (((Count(*))=1))
ORDER BY tmp.ID;
0
 
GIStewartAuthor Commented:
this works perfectly, thank you.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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