On 10/8/06, Critters <[EMAIL PROTECTED]> wrote:
Hi, I am certain this is possible with a single query, but I have had
not joy looking on google or using trial and error in MySQL...
Table1: id, name
Table2: id,member1,member2
In "Table2" the member1 and member2 are the ID's from "Table1"
Table1:
1, Dave
2, Bob
3, Simon
Table2:
1,1,2
2,2,3
3,1,3
Result wanted:
1,Dave,Bob
2,Bob,Simon
3,Dave,Simon
There are a whole bunch of fields I would want to pull from table1, but
for this example I have just used name. Can anyone point me in the right
direction?
This works:
mysql> select t2.id, t1a.name, t1b.name from table2 as t2 join table1
as t1a on (t1a.id=t2.member1) join table1 as t1b on
(t1b.id=t2.member2);
+----+------+-------+
| id | name | name |
+----+------+-------+
| 1 | dave | bob |
| 3 | dave | simon |
| 2 | bob | simon |
+----+------+-------+
3 rows in set (0.00 sec)
JP
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]