1

I need to append Table1 to Table2 in such a way that no information is duplicated.

Example:

Table1

Name   | Age
-------|-----
Jason  | 30
John   | 40
Joseph | 50
Bob    | 60

Table2

Type
--------
Dog
Cat
Fish

I need a join to produce

Name  | Age | Type
------|-----|-------
Jason | 30  | Dog
John  | 40  | Cat
Joseph| 50  | Fish
Bob   | 60  | NULL

So it only returns four rows and not 12 or more. There is no ID or other information that can relate the two tables.

Jason
  • 556
  • 1
  • 5
  • 21

2 Answers2

2

Try this:

SELECT  A.rank, A.NAME, A.AGE, B.TYPE FROM
(select @rownum:=@rownum+1 ‘rank’, T1.NAME, T1.AGE from 
TABLE1 T1, (SELECT @rownum:=0) r) A
LEFT JOIN
(select @rownum:=@rownum+1 ‘rank’, T2.TYPE from 
TABLE2 T2, (SELECT @rownum:=0) r) B
ON A.rank = B.rank

And check these:

ROW_NUMBER() in MySQL

http://craftycodeblog.com/2010/09/13/rownum-simulation-with-mysql/

http://jimlife.wordpress.com/2008/09/09/displaying-row-number-rownum-in-mysql/

Community
  • 1
  • 1
Nathan
  • 2,705
  • 23
  • 28
0

If possible try altering both tables to add index column and then join on index column this way 

ALTER table1 add id INT NOT NULL;

ALTER table2 add id INT NOT NULL;

select table1.Name, table1.Age, table2.Type from table1 inner join table2 on table1.id= table2.id
Vikram
  • 4,162
  • 8
  • 43
  • 65
  • Table2 does not have name and age columns, only type. – Joe Stefanelli Aug 24 '12 at 21:17
  • In all reality, both tables already have an ID, but they in no way relate to each other, and I don't want to add a second ID. I know there is a way to it without the extra ID, but I've forgotten how. – Jason Aug 24 '12 at 21:31
  • 2
    You want to join them based on the row order. So, you can use a row number variable to simulate this. – Nathan Aug 24 '12 at 21:51