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
