SQL query to select records from three Tables seperatly

Question

SQL query which select the record from three tables and there is no relation between these tables. Actually I want to make it a VIEW.

suppose there are three tales Table1, Table2, Table3

I want to show records of Table1 first with some filter criteria and then the records from Table2 and in last from Table3 as when we execute the view it show like the records like a Table.

There can be any number of rows but the records must be in this sequence.

Answer 1

You can do something like this - WHERE ID = 34 is just a sample filter:

create view vAllRecords as
select 1 as Rank, Field1, Field2 from Table1 where ID  = 34
UNION
select 2 as Rank, Field1, Field2 from Table2
UNION
select 3 as Rank, Field1, Field2 from Table3

The use of UNION will remove any duplicates. If you know there will be no duplicates, or you want to see them, the query will run faster with UNION ALL instead.

ORDER BY is not allowed in views, so you'll need to order by Rank when you select from the view:

select *
from vAllRecords 
order by Rank

Answer 2

you can use a UNION query:

SELECT Field1, Field2, Field3, '1' as Sequence FROM Table1 WHERE SomeCriteria
UNION
SELECT Field7, Field5, Field6, '2' FROM Table2 WHERE SomeCriteria
UNION
SELECT Field4, Field8, Field9, '3' FROM Table3 WHERE SomeCriteria

Answer 3

try:

select
   1,col1, col2, col3
   FROM Table1
   where ...
UNION ALL
select
   2,col1, col2, col3
   FROM Table2
   where ...
UNION ALL
select
   3,col1, col2, col3
   FROM Table3
   where ...
ORDER BY 1

please note that each of the three queries needs to have the same number of columns and that the data types should be consistent also. Also, I used UNION ALL to speed up the query, since there is no use eliminating duplicates between the three queries because the sequence table will guarantee no dups.

to not have the sequence column in the result set try:

SELECT
    col1,col2,col3 
    FROM (select
             1 as seq,col1, col2, col3
             FROM Table1
             where ...
          UNION ALL
          select
             2 as seq,col1, col2, col3
             FROM Table2
             where ...
          UNION ALL
          select
             3 as seq,col1, col2, col3
             FROM Table3
             where ...
         ) dt
    ORDER BY seq

Answer 4

How about:

create view AZ_VIEW as
select 1 as orderby, tbl1Col1 as col1, tbl1Col2 as col2, tbl1col3 as col3 from Table1 where criteria1='val'
union 
select 2, tbl2Col1, tbl2Col2, tbl2col3 from Table2 where criteria2='anotherval'
union 
select 3, tbl3Col1, tbl3Col2, tbl3col3 from Table3 where criteria3='athirdval'
;

Answer 5

If your tables share the same columns, you can use Union All:

Select col1, col2, 1 As seq
From table1
Union All
Select col1, col2, 2 As seq
From table1
Union All
Select col1, col2, 3 As seq
From table1
Order By seq

Answer 6

I would suggest using UNION ALL instead of union if you want all the records from each of the tables. UNION will use a distinct to filter out duplicates. If you don't need tht it is just slowing down the query.

A further explanation here: http://wiki.lessthandot.com/index.php/Union_All

To show you how to handle when you don't have all the columns in each table:

select 
   1 as seq,col1, col2, col3, cast(null as varchar (40)) as col4 
   FROM Table1 
   where ... 
UNION ALL 
select 
   2 as seq,'Unknown', col2, null, col4 
   FROM Table2 
   where ... 
UNION ALL 
select 
   3 as seq ,col1, col2, col3, cast(null as varchar (40)) as col4  
   FROM Table3 
   where ... 
ORDER BY seq

Answer 7

You can UNION the three tables, taking care to ensure that they all return the same number of fields. There is a simple cheat to control the order (seen below):

SELECT * FROM ( SELECT a, b, c, 1 as ListOrder FROM table1 UNION SELECT a, b, c, 2 as ListOrder FROM table2 UNION SELECT a, b, c, 3 as ListOrder FROM table3 ) ORDER BY ListOrder