Combining two queries into one 3

[shaminda]

I have the following sql queries. How can I write 1 query so it returns only one result set?

select distinct fdShortNumber as PartNumber, count(fdShortNumber) as Quantity
from tbStation5
where fdAssemblyDate = @fdAssemblyDate AND fdAssemblyShift = @fdAssemblyShift
group by fdShortNumber

select distinct fdShortNumber as PartNumber, count(fdShortNumber) as Quantity
from [Production1].dbo.tbStation7
where fdAssemblyDate = @fdAssemblyDate AND fdAssemblyShift = @fdAssemblyShift
group by fdShortNumber

 

[SkipVought]

same FdShortNumber VALUES in both tables?

Please post examples of data in both tables, and what result you would expect based on your examples.

 

[shaminda]

Here is the results from query1
PartNumber Quantity
70322 32
70323 113
70324 30

Here is the result from query2
PartNumber Quantity
71766 30
72003 84

I have attached sample data for both tables.

Here is what I expecting:
PartNumber Quantity
70322 32
70323 113
70324 30
71766 30
72003 84

 

[SkipVought]

How about a Union All?

Sql1
Union All
Sql2

 

[shaminda]

Thanks SkipVought for your help. I did not know it was that easy.

 

[Olaf Doschke]

It always depends on the case, what you need. In your case it was a union.

In general you can use a query as a table in outer SQL, by giving it a name, eg you have two queries, then you simply put them in brackets and add AS name, eg

(query1) as q1

and

(query2) as q2

That in itself is now just defining "tables" q1 and q2, or rather named queries, like views. Like any table or view name this now must be embedded in further query code. So now you can do whatever you could do with two tables named q1 and q1, eg join them:

Select * 
from [COLOR=#CC0000](query1) as q1[/color]
inner join [COLOR=#CC0000](query2) as q2[/color]
on q1.id= q2.id

For a union it's unnecessary to add the alias names first, as unions have no join matching clause, records are appended, not matched, but if you like straight forward writing of code you can apply this as strict rule and write:

Select * from [COLOR=#CC0000](query1) as q1[/color]
Union All
Select * from [COLOR=#CC0000](query2) as q2[/color]

This just has some redundancy, as you query all data from each query result and union that, you don't need the q1 and q2 names as reference. That's a sign you can simplify it and write it shorter as

query1
Union All
query2

But don't think it's always that simple, how to cope with subqueries depends on what operation you do with the query results, you don't always need brackets and an alias name, but it helps referencing the query result columns.

Bye, Olaf.