Hi, The below mentioned query, select eventartists.eaDnID,djnames.dnName,eventartists.eaDn2ID from eventartists,djnames where djnames.dnID = eventartists.eaDnID;
gives the following result. +--------+--------+---------+ | eaDnID | dnName | eaDn2ID | +--------+--------+---------+ | 1 | dj a | NULL | | 2 | dj b | 3 | | 4 | dj d | NULL | | 5 | dj e | NULL | +--------+--------+---------+ 4 rows in set (0.00 sec) with which we can come to know that dj b is performing with one other artist and whose ID is 3. -----Original Message----- From: Etienne [mailto:[EMAIL PROTECTED]] Sent: Monday, January 07, 2002 9:14 AM To: [EMAIL PROTECTED] Subject: Join Query Help Hello, I'm back to the list after two weeks off. I have a little problem, the email may seem long but it's clearly explained step by step what I've done... I have a table that have artists playing at events. It's called eventartists. One artist can play alone, or with another artist. Because the number of artists playing together is limited (3 max) I don't want to build another table for them since I have plenty of links like this. mysql> create table eventartists( -> eaEvtID smallint unsigned not null, -> eaDnID smallint unsigned not null, -> eaDn2ID smallint unsigned, -> unique index(eaEvtID,eaDnID,eaDn2ID), -> unique index(eaDnID, eaEvtID), -> unique index(eaDn2ID, eaEvtID)); Query OK, 0 rows affected (0.02 sec) Then I have my artists table called djnames with some info mysql> create table djnames( -> dnID smallint unsigned not null auto_increment, -> dnName varchar(20) not null, -> primary key(dnID)); Query OK, 0 rows affected (0.02 sec) then let's insert some fake data... let's fill some djs first: mysql> insert into djnames(dnName) values("dj a"); Query OK, 1 row affected (0.01 sec) mysql> insert into djnames(dnName) values("dj b"); Query OK, 1 row affected (0.00 sec) mysql> insert into djnames(dnName) values("dj c"); Query OK, 1 row affected (0.00 sec) mysql> insert into djnames(dnName) values("dj d"); Query OK, 1 row affected (0.00 sec) mysql> insert into djnames(dnName) values("dj e"); Query OK, 1 row affected (0.01 sec) then let's say event #1 has dj a, djb playing with dj c and dj d and e playing alone. mysql> insert into eventartists(eaEvtID,eaDnID) values(1,1); Query OK, 1 row affected (0.00 sec) mysql> insert into eventartists(eaEvtID,eaDnID) values(1,4); Query OK, 1 row affected (0.00 sec) mysql> insert into eventartists(eaEvtID,eaDnID) values(1,5); Query OK, 1 row affected (0.00 sec) mysql> insert into eventartists(eaEvtID,eaDnID,eaDn2ID) values(1,2,3); Query OK, 1 row affected (0.00 sec) mysql> select eaDnID,eaDn2ID from eventartists; +--------+---------+ | eaDnID | eaDn2ID | +--------+---------+ | 1 | NULL | | 2 | 3 | | 4 | NULL | | 5 | NULL | +--------+---------+ 4 rows in set (0.00 sec) So this way it's easy to see that dj b and dj c are playing together... but when putting the names in it I can't find how.. mysql> select dnName from djnames,eventartists where eaDnID = dnID or eaDn2ID=dn ID; +--------+ | dnName | +--------+ | dj a | | dj d | | dj e | | dj b | | dj c | +--------+ 5 rows in set (0.02 sec) this way i can't see when there are two djs together.. Any idea how to do so.. or how I can modify my tables so that it does so? I can see if some artists are playing together with something like: mysql> select dnName from djnames,eventartists where (eaDnID = dnID or eaDn2ID=d nID) and eaDn2ID is not null; +--------+ | dnName | +--------+ | dj b | | dj c | +--------+ 2 rows in set (0.00 sec) I'm off to bed, pleaseee help me on this one, I can't finish my event calendar without dealing with those.. Regards, Etienne --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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