Okay so I'm new to mySQL. I'm sorry this is a very novice question. Essentially I have two tables, Associates, and keys.
The content is as follows:
associates: id, department, associate, date_added
keys: id, key_name, date_added,
my code to make my dropdown is as follows:
<?php
mysql_connect('hostname', 'user', 'Password');
mysql_select_db('log');
$key_fetch = "SELECT `associates`.`department`,`associates`.`associate`,`keys`.`key_name` FROM associates , `keys` ORDER BY `key_name` DESC";
$results = mysql_query($key_fetch);
echo "<select name='key_name' size='5'>";
while ($row = mysql_fetch_array($results)) {
echo "<option value='" . $row['key_name'] . "'>" . $row['key_name'] . "</option>";
}
echo "</select>";
?>
The problem is I only have 5 keys and I have ten associates, and this creates duplicates in my dropdown and I can't fix it with SELECT DISTINCT, and I'm not too sure what else to try.
To visualize cartesian product based on above Q comments.
create table t1
( id int auto_increment primary key,
stuff1 varchar(50) not null
);
insert t1 (stuff1) values ('111.1'),('111.2'),('111.3');
create table t2
( id int auto_increment primary key,
stuff2 varchar(50) not null
);
insert t2 (stuff2) values ('222.1'),('222.2'),('222.3');
select t1.id,t1.stuff1,t2.stuff2
from t1
join t2
on t2.id=t1.id;
+----+--------+--------+
| id | stuff1 | stuff2 |
+----+--------+--------+
| 1 | 111.1 | 222.1 |
| 2 | 111.2 | 222.2 |
| 3 | 111.3 | 222.3 |
+----+--------+--------+
select t1.id,t1.stuff1,t2.stuff2
from t1,t2;
+----+--------+--------+
| id | stuff1 | stuff2 |
+----+--------+--------+
| 1 | 111.1 | 222.1 |
| 2 | 111.2 | 222.1 |
| 3 | 111.3 | 222.1 |
| 1 | 111.1 | 222.2 |
| 2 | 111.2 | 222.2 |
| 3 | 111.3 | 222.2 |
| 1 | 111.1 | 222.3 |
| 2 | 111.2 | 222.3 |
| 3 | 111.3 | 222.3 |
+----+--------+--------+
9 rows in set (0.00 sec)
select t1.id,t1.stuff1,t2.stuff2
from t1 cross join t2
So, your output, as I see it, is like B, 50 rows.