12

I'm looking for an sql answer on how to merge two tables without anything in common.

So let's say you have these two tables without anything in common:

Guys                Girls
id    name          id   name
---  ------        ----  ------
1     abraham        5    sarah
2     isaak          6    rachel
3     jacob          7    rebeka
                     8    leah

and you want to merge them side-by-side like this:

Couples

id   name      id   name
---  ------    ---  ------
1    abraham   5    sarah
2    isaak     6    rachel
3    jacob     7    rebeka
               8    leah

How can this be done?

I'm looking for an sql answer on how to merge two tables without anything in common.

Mike
  • 143
  • 1
  • 1
  • 9
  • 1
    I have several ideas, but which one is the right one depends on what you're trying to achieve here. If you just thrown them together like this, you're going to have nothing useful. Knowing the context helps to decide the Best course of action. There has to be some logic in your head about how to match them up, the trick is getting it into the DB. Are you, in your example, matching up boyfriend/girlfriend to produce a couple? – David Jul 12 '13 at 18:34
  • 2
    My question would be in the same vein as David Stratton's politer query, but a little more blunt: Why would you want to do this even if you could? – mikeY Jul 12 '13 at 18:37
  • I second this ....why? – Charleh Jul 12 '13 at 18:55
  • Okay, so it's not the best example. I'm connecting to a db that shadows an ERP with a boat load of attributes per person in various orphan tables. I know that's not supposed to happen with ERPs, but it's too much to get into. Some of these attributes are a decode of fields from lookup tables and the whole thing is unnecessarily complicated. Anyway, I have 8 queries that I cannot join for aforementioned reasons. Once I get the results need to bring them back as a single record. So, instead of hitting the db 8 times, I'm looking to do it once. – Mike Jul 12 '13 at 20:47
  • I wanted to do this in order to populate a primary key field with string keys generated by an outside source. I didn't care which record got which key, but I wanted all 20,000 records to have a key. Just like pasting one column of a spreadsheet next to another one. – Noumenon Feb 27 '19 at 00:06
  • if you use ancient reporting tools like Crystal Reports (which can't handle more than 1 table at a time), then you have to do this type of kludge, unfortunately. SSRS was invented to handle multiple queries from multiple data sources, tables, DBs, services, simultaneously. – MC9000 May 05 '22 at 17:03

4 Answers4

22

You can do this by creating a key, which is the row number, and joining on it.

Most dialects of SQL support the row_number() function. Here is an approach using it:

select gu.id, gu.name, gi.id, gi.name
from (select g.*, row_number() over (order by id) as seqnum
      from guys g
     ) gu full outer join
     (select g.*, row_number() over (order by id) as seqnum
      from girls g
     ) gi
     on gu.seqnum = gi.seqnum;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
6

Just because I wrote it up anyway, an alternative using CTEs;

WITH guys2 AS ( SELECT id,name,ROW_NUMBER() OVER (ORDER BY id) rn FROM guys), 
    girls2 AS ( SELECT id,name,ROW_NUMBER() OVER (ORDER BY id) rn FROM girls)
SELECT  guys2.id guyid,   guys2.name guyname, 
       girls2.id girlid, girls2.name girlname
FROM guys2 FULL OUTER JOIN girls2 ON guys2.rn = girls2.rn
ORDER BY COALESCE(guys2.rn, girls2.rn);

An SQLfiddle to test with.

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
3

Assuming, you want to match guys up with girls in your example, and have some sort of meaningful relationship between the records (no pun intended)...

Typically you'd do this with a separate table to represent the association (relationship) between the two.

enter image description here

This wouldn't give you a physical table, but it would enable you to write an SQL query representing the final results:

SELECT     Girls.ID AS GirlId, Girls.Name AS GirlName, Guys.ID AS GuyId, Guys.Name AS GuyName
FROM         Couples INNER JOIN
             Girls ON Couples.GirlId = Girls.ID INNER JOIN
             Guys ON Couples.GuyId = Guys.ID

which you could then use to create a table on the fly using the Select Into syntax

  SELECT     Girls.ID AS GirlId, Girls.Name AS GirlName, Guys.ID AS GuyId, Guys.Name AS GuyName
    INTO MyNewTable
    FROM         Couples INNER JOIN
                 Girls ON Couples.GirlId = Girls.ID INNER JOIN
                 Guys ON Couples.GuyId = Guys.ID

(But standard Normalization rules would say it's best to keep them in distinct tables rather than creating a temp table, unless there's a performance reason not to do so.)

David
  • 72,686
  • 18
  • 132
  • 173
-1

I need this all the time, -- creating templates in Excel using input from my tables. This pulls from one table that has my regions, the other with the quarters in a year. the result gives me one region name for each quarter/period.

SELECT b.quarter_qty, a.mkt_name FROM TBL_MKTS a, TBL_PERIODS b

kim
  • 9