Hi,
The better way of storage is to normalize it.
Hope this way it will help you out.
SQL> DESC EVENTS
Name Null? Type
----------------------------------------- --------
----------------------------
EVENTID NUMBER(2)
EVENTPART NUMBER(2)
ARTISTID NUMBER(2)
SQL> DESC ARTISTS
Name Null? Type
----------------------------------------- --------
----------------------------
ARTISTID NUMBER(2)
ARTISTNAME VARCHAR2(20)
SQL> SELECT * FROM EVENTS;
EVENTID EVENTPART ARTISTID
---------- ---------- ----------
1 1 1
1 2 2
1 2 3
1 3 4
1 4 5
ABOVE THE SAME EVENT HAS FOUR PARTS.
PART1: ARTIST 1
PART2: ARTIST 2,3
PART3: ARTIST 4
PART4: ARTIST 5
SQL> SELECT * FROM ARTISTS;
ARTISTID ARTISTNAME
---------- --------------------
1 A
2 B
3 C
4 D
5 E
SQL> SELECT A.EVENTID,A.EVENTPART,A.ARTISTID,B.ARTISTNAME FROM EVENTS
A,ARTISTS B
2 WHERE A.ARTISTID=B.ARTISTID;
EVENTID EVENTPART ARTISTID ARTISTNAME
---------- ---------- ---------- -------------------- ---------------------
1 1 1 A
1 2 2 B
1 2 3 C
1 3 4 D
1 4 5 E
The rest is to retrieve the artists of each part of the event. This way we
can identify the events, parts and also the names of the artists.
I don't think u ll be using mysql as GUI. The retrieval and display from the
result set can be done comfortably through any programming language.
Hope this would help you.
Thanks,
Rama Raju
-----Original Message-----
From: udayashankarl_n [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 07, 2002 9:38 AM
To: Etienne; [EMAIL PROTECTED]
Subject: RE: Join Query Help
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
---------------------------------------------------------------------
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
