Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have some SQL queries that retrun the following.

  • Total Pre Orders Web
  • Total Pre Orders Phone
  • Total Orders Web
  • Total Orders phone

Each one of those queries retrurns a the query name and the count of the query. The query name ahs been addded as just text in the select part.

If I just want to return all the pre orders I can do

Total Pre Orders Web
UNION
Total Pre Orders Phone

This will return

Total Pre Orders Web    | 10
Total Pre Orders Phone  | 2

How would I go about doing something like

Total Pre Orders Web    | 10 | Total Orders Web    | 20
Total Pre Orders Phone  | 2  | Total Orders Phone  | 0

SO I want to do a Union to the side?

share|improve this question
    
What does the table look like? This is a JOIN, but need to know more... –  gbn Apr 3 '12 at 12:58
add comment

2 Answers

up vote 3 down vote accepted

If I understand you correctly you have four different queries and you want the result from two of them on the first row and the other two on the second row.

You can do sub-queries something like this:

select 'Total Pre Orders Web',
       (select 10 /*Replace this with your query*/), 
       'Total Orders Web',
       (select 20 /*Replace this with your query*/)
union all
select 'Total Pre Orders Phone',
       (select 2 /*Replace this with your query*/),
       'Total Orders Phone',
       (select 0 /*Replace this with your query*/)

Result:

---------------------- ----------- ------------------ -----------
Total Pre Orders Web   10          Total Orders Web   20
Total Pre Orders Phone 2           Total Orders Phone 0
share|improve this answer
add comment

It's called a JOIN. Take this example scenario for instance:

create table MediaType
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

insert into MediaType(Name)
select 'Web'
union all
select 'Phone'
go

create table Orders
(
    id int identity(1, 1) not null,
    Name varchar(100) not null,
    fkMediaType int foreign key references MediaType(id),
    TotalOrders int not null
)
go

insert into Orders(Name, fkMediaType, TotalOrders)
select 'First Orders', 1, 10
union all
select 'Second Orders', 2, 20
go

create table PreOrders
(
    id int identity(1, 1) not null,
    Name varchar(100) not null,
    fkMediaType int foreign key references MediaType(id),
    TotalOrders int not null
)
go

insert into PreOrders(Name, fkMediaType, TotalOrders)
select 'First PreOrders', 1, 0
union all
select 'Second PreOrders, 2, 2
go

Then to get your data, you'd do something like this:

select
    o.Name + ' : ' + m.Name,
    o.TotalOrders,
    p.Name + ' : ' + m.Name,
    p.TotalOrders
from Orders o
inner join PreOrders p
on o.fkMediaType = p.fkMediaType
inner join MediaType m
on o.fkMediateType = m.id
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.