SQL Union syntax in VBScript?

usslindstrom
usslindstrom used Ask the Experts™
on
Experts, I have the 3 following individual queries that I run from VB against SQL.

I'm trying to figure out how to make them a "UNION SELECT" statement, but can't get the syntax right.

Would somebody know how I could update the 3 individual query strings into a single one? - that way I can do a DISTINCT against the query to drop duplicates.

Thanks for any information you can provide.
objRecordSet.Open "SELECT PrinterName, DriverFileName, DriverTextString, ModelNumber FROM " & SQLTable_PrinterMap_Global, _
            objConnection, adOpenStatic, adLockOptimistic


    objRecordSet.Open "SELECT PrinterName, DriverFileName, DriverTextString, ModelNumber FROM " & SQLTable_PrinterMap_Room & " WHERE RoomNumber = '" & ComputerLocation & "'", _
            objConnection, adOpenStatic, adLockOptimistic


    objRecordSet.Open "SELECT PrinterName, DriverFileName, DriverTextString, ModelNumber FROM " & SQLTable_PrinterMap_Computer & " WHERE ComputerName = '" & ComputerName & "'", _
            objConnection, adOpenStatic, adLockOptimistic

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Software Team Lead
Commented:
try like:


SQL = "SELECT DISTINCT * FROM ( " & _
"SELECT PrinterName, DriverFileName, DriverTextString, ModelNumber FROM " & SQLTable_PrinterMap_Global & " " & _
"Union " & _
"SELECT PrinterName, DriverFileName, DriverTextString, ModelNumber FROM " & SQLTable_PrinterMap_Room & " WHERE RoomNumber = '" & ComputerLocation & "' " & _
"Union " & _
"SELECT PrinterName, DriverFileName, DriverTextString, ModelNumber FROM " & SQLTable_PrinterMap_Computer & " WHERE ComputerName = '" & ComputerName & "' " & _
") a "


objRecordSet.Open SQL, _
            objConnection, adOpenStatic, adLockOptimistic



Or use OR clauses in your SQL statement.
usslindstrom

Author

Commented:
That is absolutely perfect!

Thank you very much.  It looks like I almost had the same text in my sql string, but I was missing some quotation marks around items.

Much appreciated!

*On note, I do like you you turned the whole SQL statement into a variable.  Much easier to read.  :o

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial