CODEWITHSUNDEEP
sqlite
Michael
Michael

Reputation: 9402

simple combining of two tables into one containing the columns of both

Is there a way to combine two tables in a 1:1 manner? It seems like what a JOIN really does is create all the possible combinations between rows in two tables, then use ON to filter out the rows that are not of interest. But what I really want is to combine two arbitrary tables like so:

 a        b         a | b
---   +  ---   =>  ---+---
 1        8         1 | 8
 9        3         9 | 3
 7        4         7 | 4
 5        0         5 | 0

The individual tables might be formed by a selected or they might be literal values created by SELECT/UNIONing literal values. The solution should not depend on any particular way that both tables are obtained, but can assume that both tables will always have the same number of rows.

For example, suppose the first column be found by:

SELECT value FROM table ORDER BY x ASC

And the second column be found by:

SELECT * FROM (SELECT 8 UNION SELECT 3 UNION SELECT 4 UNION SELECT 0),(SELECT 1 UNION SELECT 9 UNION SELECT 7 UNION SELECT 5 as B)

Now if I do this query:

SELECT * FROM
    (SELECT value FROM table ORDER BY x ASC) AS a,
    (SELECT 1 UNION SELECT 9 UNION SELECT 7 UNION SELECT 5 as B)

I get every 16 rows returned, pairing every a value with every b value, when in fact I just want 4 rows as shown in the table above. Furthermore, I can't think of an ON conditioner to make it filter out the rows I don't want because the tables don't share anything in common.

UNION won't work, because it unions rows together. What I want to do is more like unioning columns together.

I tried the following:

SELECT * FROM
    (SELECT value FROM table ORDER BY x ASC) AS a,
    (SELECT 1 UNION SELECT 9 UNION SELECT 7 UNION SELECT 5 as B)
ON
    a.rowid == B.rowid

But this only seems to work if both sub-selects were manually created (e.g. the same way as the second clause).

Upvotes: 1

Views: 238

Answers (2)

user3723977
user3723977

Reputation:

Have you taken a look at this Stack Overflow answer:

What about this one?"

In the OP's post, you give 4 rows from 2 tables with unique rows. If you use a statement like this:

SELECT 
  t1.a
UNION
  t2.b

it will yield you 4 unique rows (just as you asked). See here for an explanation of how a UNION would work for you in this example b/c you have no duplicates of info in your 2 tables... while a UNION ALL would yield 8 rows, a UNION should yield 4 rows (in this case only):

http://en.wikipedia.org/wiki/Set_operations_(SQL)

Now, this may not work for other, non-unique rows, but that's not what you asked.

In any event, the 2nd link I supplied gives you examples and instructions for dumping one table into another, which is essentially the only way to do this, as a SQL statement is going to work relationally on 2 tables which is why you are having problems.

You are basically trying to combine 2 tables WITHOUT any real relationship between the 2 of them, so what you really want/need is to be doing a concatenation of some sort... and then reparse them back into columns. You have a few options in this regard:

  • It's best to use an ETL tool (or script) of some kind, then...
  • concat into a text file perhaps,
  • then reimport into a table using a column delimiter that you set/injected between the columns when you concatenated them.

Short of a tool, or an external script to concatenate your result sets as text files, try using the SQL examples that I provided in the links above.

Upvotes: 1

Hristo Valkanov
Hristo Valkanov

Reputation: 1687

You can try creating 2 temporary tables with ID's and then join them based on a AUTO INCREMENT field.

Upvotes: 1

Related Questions