SQL Question...

Hello,

I have a union query and need it have the combined items into a list box. I can only get the items from the SQL statement prior to the UNION part.

Sql = "SELECT EquipmentType FROM tbl_Equipment " _
    & "Where EquipmentType <> Null And Trim(EquipmentType) <> "" " _
    & "Union " _
    & "SELECT fld_EqType FROM tbl_Inventory_Equipment " _
    & "WHERE fld_EqType <> NULL and TRIM(fld_EqType) <> "" ;"
   
    g_rst.CursorLocation = adUseClient
    g_rst.Open sSqlx, g_conn, adOpenKeyset, adLockOptimistic
   
    Do While Not g_rst.EOF
        Me.cboEquipmentType.AddItem g_rst("EquipmentType").Value

        Me.cboEquipmentType.AddItem g_rst("fld_EqType").Value

        g_rst.MoveNext
    Loop

Also, using the example above, how can I union three tables?

Table3: Three_tbl
Field3: Three_fld

Thanks,

ADawn




ADawnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tppradeep18Commented:
hi ADawn,

When a union query run, the recordset contains field names belonging to the querty in the first part of the union. The resultset will contain results from both the table. Suppose You have two tables Sisters and brothers. Sisters contain SName field and Brothers Contain BName field. Sister table has one record 'Alisha' and Brother table has one record 'Martin' (BOTh fields have same Data type). No run the following union querry

select SName from sisters union select BName from brothers

This will contain recordset as follows

SName
------

Alisha
Martin

As you see the recorset contains results from both table but the field name SNAme is from the first table sisters.


to UNION a third table use the followin query

select SName from sisters union select BName from brothers union select SBName from StepBrother



The syntax is as follows

{ < query specification > | ( < query expression > ) }
        UNION [ ALL ]
        < query specification | ( < query expression > )
            [ UNION [ ALL ] < query specification | ( < query expression > )
                [ ...n ] ]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mdouganCommented:
well said, tppradeep18.

it is important to repeat that the columns in all selects that are part of a union must match in number and in datatype exactly.  For example these would be invalid unions:

select sname from sisters
union
select age from brothers


select sname, dress_size from sisters
union
select bname from brothers

However, you can fake it:

select sname, dress_size from sisters
union
select bname, 0 from brothers

Last but not least, if you have to order the results, you have to do it after the last select statement in the union:


select sname, dress_size from sisters
union
select bname, pants_size from brother
order by sname, dress_size

or
order by 1,2   (if you want it to be less confusing

0
mlmccCommented:
listening
0
ADawnAuthor Commented:
With the help of mdougan it works. Thanks, ADawn
0
ADawnAuthor Commented:
With the help of mdougan it works. Thanks, ADawn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.