Subject Re: [ib-support] How to join results of two queries.
Author Neil Murby
Set, Hans,
Thankyou very much, You've got me sorted.

A note for others who have never used unions before...
The results sets returned be the two selects in the union must be of
the same datatype(s).
So for example, the query presented below needs a cast around the fixed
strings to make them the same data type
ie: cast('staff' as varchar(7)) and in the second select:
cast('contact' as varchar(7)).


>>> svein.erling.tysvaer@... 6/1/2003 6:15:51 pm >>>
as Hans wrote, use a union. Also, remove RIGHT from your queries unless
have only shown us a subset of your queries. You want an inner join and

using RIGHT (or LEFT) changes it to an outer join.

>select staff.staffid as id, staff.lastname as name, 'staff' as
>from staff join liststaff on staff.staffid = liststaff.staffid
>where liststaff.ListID = :ListID
>select contact.contactid as id, contact.lastname as name, 'contact'
>from contact join listcontacts on contact.contactid =
>where listcontacts.ListID = :ListID
>order by 2



(1) The contents of this e.mail and its attachments are confidential and privileged. Any unauthorised use of the contents is expressly prohibited. If you receive this e.mail in error, please contact us, then delete the e.mail.
(2) Before opening or using attachments, check them for viruses and defects. The contents of this e.mail and its attachments may become scrambled, truncated or altered in transmission. Please notify us of any anomalies.
(3) Our liability is limited to resupplying the e.mail and attached files or the cost of having them resupplied.
(4) Minter Ellison collects personal information to provide and market our services. For more information about use, disclosure and access see our privacy policy at