vbnetcoder
asked on
sql query - to queries one result set
I want to run two queries with one result set so i might have
Select firstname from Names
Select Company name from Company
With the result being
firstnames company
name1 abc comp
name 2 wel company
Select firstname from Names
Select Company name from Company
With the result being
firstnames company
name1 abc comp
name 2 wel company
Shaun Kline
Do you have a common field between the two tables? Without it, your results will be a match of every first name with every company name.
ASKER
long story but that is fine.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
just to be clear
if
Select firstname from Names
gives
firstname
tom
dick
harry
and
Select Company name from Company
gives
company name
ibm
walmart
enron
what are you desiring?
firstname company
tom ibm
dick walmart
harry enron
?
if you don't have linking columns between the two tables then that is impossible to guarantee as a result set.... (a consistent , reproducible one ...)
the select firstname,company from [name],company solution
will return 9 rows with the above data not 3....
if you want to just match up the individual resultsets and display them side by side then this format may work for you
select a.* ,b.*
from (select .....
,row_number() over ( order by .... ) as rn
from names) as a
full outer join
(select ....
,row_number() over ( order by ... ) as rn1
from company) as b
on a.rn=b.rn1
order by coalesce(a.rn,b.rn1) asc
using the row_number() windowing function ...
the over clause can either be empty e.g over () or you can specify an order by clause to determine the sequence used to determine the row sequencing....
over() will be a "random" sequence (although likely to be physical table order)
does this help?
for more assistance please confirm which DBMS and version you are using...
if
Select firstname from Names
gives
firstname
tom
dick
harry
and
Select Company name from Company
gives
company name
ibm
walmart
enron
what are you desiring?
firstname company
tom ibm
dick walmart
harry enron
?
if you don't have linking columns between the two tables then that is impossible to guarantee as a result set.... (a consistent , reproducible one ...)
the select firstname,company from [name],company solution
will return 9 rows with the above data not 3....
if you want to just match up the individual resultsets and display them side by side then this format may work for you
select a.* ,b.*
from (select .....
,row_number() over ( order by .... ) as rn
from names) as a
full outer join
(select ....
,row_number() over ( order by ... ) as rn1
from company) as b
on a.rn=b.rn1
order by coalesce(a.rn,b.rn1) asc
using the row_number() windowing function ...
the over clause can either be empty e.g over () or you can specify an order by clause to determine the sequence used to determine the row sequencing....
over() will be a "random" sequence (although likely to be physical table order)
does this help?
for more assistance please confirm which DBMS and version you are using...
ASKER
ty