Combine two tables that have no common fields

Question

I want to learn how to combine two db tables which have no fields in common  I ve checked UNION but MSDN says    The following are basic rules for combining the result sets of two queries by using UNION   The number and the order of the columns must be the same in all queries  The data types must be compatible    But I have no fields in common at all  All I want is to combine them in one table like a view  So what should I do

User · Answer

Please try this query   Combine two tables that have no common columns   SELECT    FROM table1   UNION   SELECT    FROM table2   ORDER BY orderby ASC

User · Answer

This is a very strange request  and almost certainly something you d never want to do in a real-world application  but from a purely academic standpoint it s an interesting challenge  With SQL Server 2005 you could use common table expressions and the row number   functions and join on that   with OrderedFoos as       select row number   over  order by FooName  RowNum        from Foos  nolock     OrderedBars as       select row number   over  order by BarName  RowNum        from Bars  nolock    select    from OrderedFoos f     full outer join OrderedBars u on u RowNum   f RowNum   This works  but it s supremely silly and I offer it only as a  community wiki  answer because I really wouldn t recommend it

User · Answer

SELECT   FROM table1  table2   This will join every row in table1 with table2  the Cartesian product  returning all columns

User · Answer

select      status id       status       null as path       null as Description  from      zmw t status  union select      null       null       path as cid       Description from zmw t path

User · Answer

Select  DISTINCT  t1 col t2col From table1 t1  table2 t2  OR  Select  DISTINCT  t1 col t2col From table1 t1  cross JOIN  table2 t2   if its hug data   its take long time

User · Answer

SELECT t1 col table1col  t2 col table2col FROM table1 t1  JOIN table2 t2 on t1 table1Id   x and t2 table2Id   y

User · Answer

There are a number of ways to do this  depending on what you really want  With no common columns  you need to decide whether you want to introduce a common column or get the product   Let s say you have the two tables   parts               custs   ---- ----------     ----- ------    id   desc            id   name    ---- ----------     ----- ------     1   Sprocket       100   Bob       2   Flange         101   Paul    ---- ----------     ----- ------    Forget the actual columns since you d most likely have a customer order part relationship in this case  I ve just used those columns to illustrate the ways to do it   A cartesian product will match every row in the first table with every row in the second    gt  select   from parts  custs        id desc     id  name       -- ----     --- ----       1  Sprocket 101 Bob       1  Sprocket 102 Paul       2  Flange   101 Bob       2  Flange   102 Paul   That s probably not what you want since 1000 parts and 100 customers would result in 100 000 rows with lots of duplicated information   Alternatively  you can use a union to just output the data  though not side-by-side  you ll need to make sure column types are compatible between the two selects  either by making the table columns compatible or coercing them in the select     gt  select id as pid  desc  null as cid  null as name from parts   union   select null as pid  null as desc  id as cid  name from custs      pid desc     cid name     --- ----     --- ----                  101 Bob                   102 Paul     1   Sprocket     2   Flange   In some databases  you can use a rowid rownum column or pseudo-column to match records side-by-side  such as   id desc     id  name -- ----     --- ---- 1  Sprocket 101 Bob 2  Flange   101 Bob   The code would be something like   select a id  a desc  b id  b name from parts a  custs b where a rownum   b rownum    It s still like a cartesian product but the where clause limits how the rows are combined to form the results  so not a cartesian product at all  really    I haven t tested that SQL for this since it s one of the limitations of my DBMS of choice  and rightly so  I don t believe it s ever needed in a properly thought-out schema  Since SQL doesn t guarantee the order in which it produces data  the matching can change every time you do the query unless you have a specific relationship or order by clause   I think the ideal thing to do would be to add a column to both tables specifying what the relationship is  If there s no real relationship  then you probably have no business in trying to put them side-by-side with SQL   If you just want them displayed side-by-side in a report or on a web page  two examples   the right tool to do that is whatever generates your report or web page  coupled with two independent SQL queries to get the two unrelated tables  For example  a two-column grid in BIRT  or Crystal or Jasper  each with a separate data table  or a HTML two column table  or CSS  each with a separate data table

User · Answer

Very hard when you have to do this with three select statments  I tried all proposed techniques up there but it s in-vain  Please see below script  please advice if you have alternative solution     select distinct x best Achiver ever y Today best Achiver  z Most Violator from        SELECT  Top 4  ROW NUMBER   over  order by tl username   AS conj  tl         username     -- gt     str count     as best Achiver ever      FROM TiketFollowup   dbo  N FCR Tikect Log Archive tl      group by tl username      order by count    desc  x       left outer join    SELECT  Top 4  ROW NUMBER   over  order by tl username   as conj  tl  username     -- gt     str count     as Today best Achiver FROM TiketFollowup   dbo   N FCR Tikect Log  tl where convert date  tl stamp  121    convert date GETDATE   121  group by tl username order by count    desc  y  on x conj y conj   left outer join     select  ROW NUMBER   over  order by count     as conj username   -- gt      str  count dbo IsViolated stamp    as Most Violator from N FCR Ticket where dbo IsViolated stamp     violated  and  convert date stamp  121   lt  convert date GETDATE   121  group by username order by count    desc  z on x conj   z conj

User · Answer

select   from this table   select distinct person from this table  union select address as location from that table   drop wrong table from this database

User · Answer

why don t you use simple approach      SELECT distinct       FROM      SUPPLIER full join      CUSTOMER on           CUSTOMER OID   SUPPLIER OID         It gives you all columns from both tables and returns all records from customer and supplier if Customer has 3 records and supplier has 2 then supplier ll show NULL in all columns

User · Answer

To get a meaningful useful view of the two tables  you normally need to determine an identifying field from each table that can then be used in the ON clause in a JOIN     THen in your view   SELECT T1    T2   FROM T1 JOIN T2 ON T1 IDFIELD1   T2 IDFIELD2   You mention no fields are  common   but although the identifying fields may not have the same name or even be the same data type  you could use the convert   cast functions to join them in some way

User · Answer

try   select   from table 1 left join table2 as t on 1   1    This will bring all the columns from both the table

User · Answer

Joining Non-Related Tables    Demo SQL Script  IF OBJECT ID  Tempdb   T1   IS NOT NULL  DROP TABLE  T1   CREATE TABLE  T1  T1 Name VARCHAR 75     INSERT INTO  T1  T1 Name  VALUES   Animal     Bat     Cat     Duet     SELECT   FROM  T1   IF OBJECT ID  Tempdb   T2   IS NOT NULL  DROP TABLE  T2   CREATE TABLE  T2  T2 Class VARCHAR 10     INSERT INTO  T2  T2 Class  VALUES   Z     T     H      SELECT   FROM  T2    To Join Non-Related Tables   we are going to introduce one common joining column of Serial Numbers like below   SQL Script  SELECT T1 T1 Name ISNULL T2 T2 Class     AS T2 Class FROM   SELECT T1 Name ROW NUMBER   OVER ORDER BY  SELECT NULL   AS S NO FROM  T1  T1 LEFT JOIN   SELECT T2 Class ROW NUMBER   OVER ORDER BY  SELECT NULL   AS S NO FROM  T2  T2 ON t1 S NO T2 S NO

User · Answer

If the tables have no common fields then there is no way to combine the data in any meaningful view  You would more likely end up with a view that contains duplicated data from both tables

[sql-server] Combine two tables that have no common fields

Examples related to sql-server

Examples related to union