Is it possible to append an entirely unique, second query to the end of the results of the first?

1

The image above explains the format I would like but entirely over-simplified to save space. The image above represents a single set of results from two unique queries in a single tab in dbviz.

My queries are as follows:

Select *
from
am_revenue.adtagref
where (ad_tag_name like '1%');
Select *
from
am_revenue.adtagref
where (ad_tag_name like '2%')

Normally, dbviz will just create two tabs of results when I run these queries at the same time. How do I force them into the same tab but with the second results appended to the right of the first results?

2

You can use UNION to join the results of two queries:
SELECT_query_1 U-N-I-O-N SELECT_query_2

The restriction is that both queries must have the same number of columns and each corresponding column has to have the same or implicitly (automatically) compatible data type. That is, query 1 column 1 and query 2 column 1 data types must match, q1 c2 and q2 c2 types must match, etc., for all the columns.

See "Books Online" under "UNION" for more details and/or restrictions.

3

Scott, do you mean something like this?

select * from
am_revenue.adtag_ref
where (ad_tag_name like 'f%')
union
select * from
am_revenue.adtag_ref
where (ad_tag_name like 'g%')

Or something different? The above query will put them on top of each other. I need the results to be side by side.

Thanks again.

4

Sorry, try this:

SELECT 
    tag_f.ID AS ID1, tagf.adtagname AS adtagname1,
    tag_g.ID AS ID2, tagg.adtagname AS adtagname2
FROM (
    select *, row_number() over(order by id) as row_num
    from am_revenue.adtag_ref
    where (ad_tag_name like 'f%') 
) as tag_f
FULL OUTER JOIN (
    select *, row_number() over(order by id) as row_num 
    from am_revenue.adtag_ref
    where (ad_tag_name like 'g%')
) AS tag_g ON tag_g.row_num = tag_f.row_num
ORDER BY tag_f.ID
5

With our version of SQL we are unable to use full outer joins. Is there a workaround?

Thanks!

6

Put whichever query has more results first and do a LEFT OUTER JOIN rather than a FULL OUTER JOIN.

7

Scott, I couldn't get it to work. I know little about sql and will try to figure it out but if you have any further guidance that would be nice.

Thanks

8 
SELECT 
    tag_f.ID AS ID1, tagf.adtagname AS adtagname1,
    tag_g.ID AS ID2, tagg.adtagname AS adtagname2
FROM (
    select *, row_number() over(order by id) as row_num 
    from am_revenue.adtag_ref
    where (ad_tag_name like 'g%')
) AS tag_g
LEFT OUTER JOIN (
    select *, row_number() over(order by id) as row_num
    from am_revenue.adtag_ref
    where (ad_tag_name like 'f%') 
) as tag_f ON tag_g.row_num = tag_f.row_num
ORDER BY tag_f.ID
9

What version / flavour of SQL?

This is a Microsoft SQL Server forum, so folk here may not be familiar with other Flavours of SQL ... The FULL JOIN existed back in Version 2000 of MS SQL Server.