What you need is to somehow get an additional column with the rows numbered. You can do this by creating a temporary table with an AUTO_INCREMENT column and inserting the records from the original table. Of course, you need an ORDER BY clause on the INSERT ... SELECT, because SQL doesn't know or care what order the rows are supposed to be in.
Once you have done this, you can join the two temporary tables on the count row. Here's an example for one of your tables: mysql> select * from b; +------+-------+ | seq | name | +------+-------+ | 9 | Kelly | | 3 | Jack | | 1 | Bob | | 7 | Janet | | 8 | Gary | +------+-------+ 5 rows in set (0.00 sec) mysql> create temporary table tb (row int auto_increment primary key, seq int,name varchar(20)); Query OK, 0 rows affected (0.02 sec) mysql> insert into tb (seq,name) select * from b order by seq; Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from tb; +-----+------+-------+ | row | seq | name | +-----+------+-------+ | 1 | 1 | Bob | | 2 | 3 | Jack | | 3 | 7 | Janet | | 4 | 8 | Gary | | 5 | 9 | Kelly | +-----+------+-------+ 5 rows in set (0.00 sec) mysql> > From: "Jeff Habermann" <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: Help with JOIN query > Date: Thu, 28 Mar 2002 17:16:05 -0800 > > What i want to do seems simple, but i can not for the life of me figure out > how to do it. > Tables: These tables are not related in any way. > > A > -- > 1 > 2 > 3 > 4 > 5 > > B > -- > 1,Bob > 3,Jack > 7,Janet > 8,Gary > 9,Kelly > > I would like to combine tables A and B with a SELECT statement to get this: > > SELECT > ------ > 1,1,Bob > 2,3,Jack > 3,7,Janet > 4,8,Gary > 5,9,Kelly > > So basically i want to join two tables, but i dont have anything to match > on...Basically, i am trying to take a column from one table and add it to > another table for a SELECT statement. I am not sure if JOIN is even what i > am looking for here. To do this, i am assuming both tables have the exact > same number of records. > Any ideas on how i can do this? > > Thanks, > Jeff --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
