Need help with 4 table join

[Wysocki]

I'm a mysql newbie and am stuck writing a complicated (to me) join statement. I have YMCA project where many programs are offered based upon a variable given location and agegroup. I created three tables with code numbers and their descriptions, and a matrix table that contains all the possibilities available. Here's my tables:

TABLE=AGEGROUPS
AGENUM AGENAME
1      Children
2      Teens
3      Adults

TABLE=LOCATIONS
LOCNUM LOCNAME
1      Azusa
2      Baldwin
3      Covina

TABLE=PROGRAMS
PROGNUM PROGNAME
1       Basketball
2       Fencing
3       Swimming

TABLE=MATRIX
PROGNUM LOCNUM AGENUM
1       3      1
1       3      2
1       2      1
2       1      3
2       2      1

In PHP, I collect two variables ($agename & $locname) which I want to use in the select statement, returning the name(s) of the programs available to them according to the matrix. So, if I had $agename="Children" and $locname="Baldwin", I'd return "Basketball" and "Fencing" from the query. Also, I want them grouped by progname.

Thanks for any help in advance, I'm desperate!

[waveclaw]

You have a very interesting database there. Fortunately, the 'MATRIX' table looks to have a potential compound key in locnum, agenum. Something like a doubly nested SQL statement with plenty of implicit joins would work:

Pseudocode for something like this might work:

select progname from programs sort by progname where prognum = pn from 
    (select prognum as pn from matrix where locnum = ln and agenum = an from
      (select agenum as an from agegroups where agename = $agename,  
       select locnum as ln from locations where locname = $locname
      )
   )

I'm not sure, but I will guarantee horrible performance on a query of this type with any sizable database.

To break it down, you get the location numbers and age numbers from their tables. Use those on the matrix table to get the program number. Use the program number to index which programs you want.

This is like playing an old SCUMVM game where you need to pick up item A to get B to use C to ... and then unlock XYZ to finish PQTW and maybe win if you remember to carry item TLGQ from room 1 the whole time.

[DJMaze]

Use "INNER JOIN" this makes everything a "must have"

SELECT progname FROM programs AS p
INNER JOIN matrix AS m ON (m.prognum = p.prognum)
INNER JOIN locations AS l ON (l.locnum = m.locnum)
INNER JOIN agegroups AS a ON (a.agenum = m.agenum)
WHERE a.agename='' AND locname=''

There are many other ways using "HAVING" or "USING" or even without JOIN's

SELECT progname
FROM programs AS p,
     matrix AS m,
     locations AS l,
     agegroups AS a
WHERE
...

[landonmkelsey]

first time I ever encountered a use for the cartesian product

it generates all possible combinations

[landonmkelsey]

first time I ever encountered a use for the cartesian product

it generates all possible combinations

combinatorial analysis says 3 to the 3rd power = 27

will this help?

drop table agegroups;
drop table locations;
drop table programs;
create table agegroups (agenum int , agename varchar(16) );
create table locations (locnum int , locname varchar(16) );
create table programs (prognum int , progname varchar(16) );

insert into locations values(1,'azuza');
insert into locations values(2,'baldwin');
insert into locations values(3,'covina');

insert into programs values(1,'basketball');
insert into programs values(2,'fencing');
insert into programs values(3,'swimming');

insert into agegroups values(1,'children');
insert into agegroups values(2,'teens');
insert into agegroups values(3,'adults');

select * from locations cross join programs cross join agegroups;

select * from locations, programs, agegroups;

select PROGNUM,LOCNUM,AGENUM from locations, programs, agegroups;
result of last select:

mysql> select PROGNUM,LOCNUM,AGENUM from locations, programs, agegroups;
+---------+--------+--------+
| PROGNUM | LOCNUM | AGENUM |
+---------+--------+--------+
|       1 |      1 |      1 |
|       1 |      2 |      1 |
|       1 |      3 |      1 |
|       2 |      1 |      1 |
|       2 |      2 |      1 |
|       2 |      3 |      1 |
|       3 |      1 |      1 |
|       3 |      2 |      1 |
|       3 |      3 |      1 |
|       1 |      1 |      2 |
|       1 |      2 |      2 |
|       1 |      3 |      2 |
|       2 |      1 |      2 |
|       2 |      2 |      2 |
|       2 |      3 |      2 |
|       3 |      1 |      2 |
|       3 |      2 |      2 |
|       3 |      3 |      2 |
|       1 |      1 |      3 |
|       1 |      2 |      3 |
|       1 |      3 |      3 |
|       2 |      1 |      3 |
|       2 |      2 |      3 |
|       2 |      3 |      3 |
|       3 |      1 |      3 |
|       3 |      2 |      3 |
|       3 |      3 |      3 |
+---------+--------+--------+
27 rows in set (0.00 sec)

mysql>

run the other select statements!

I am still working on this to make SURE you get what you want!

[landonmkelsey]

Is this it?

mysql> select PROGname,LOCname,AGEname from locations, programs, agegroups order by progname;
+------------+---------+----------+
| PROGname | LOCname | AGEname |
+------------+---------+----------+
| basketball | azuza | adults |
| basketball | azuza | teens |
| basketball | azuza | children |
| basketball | baldwin | adults |
| basketball | baldwin | teens |
| basketball | baldwin | children |
| basketball | covina | adults |
| basketball | covina | teens |
| basketball | covina | children |
| fencing | azuza | teens |
| fencing | azuza | children |
| fencing | baldwin | adults |
| fencing | baldwin | teens |
| fencing | baldwin | children |
| fencing | covina | adults |
| fencing | covina | teens |
| fencing | covina | children |
| fencing | azuza | adults |
| swimming | covina | adults |
| swimming | covina | teens |
| swimming | covina | children |
| swimming | azuza | adults |
| swimming | azuza | teens |
| swimming | azuza | children |
| swimming | baldwin | adults |
| swimming | baldwin | teens |
| swimming | baldwin | children |
+------------+---------+----------+
27 rows in set (0.00 sec)

mysql>