How to Select Multiple Rows with Same "ID" from LookUp Table 1

[tyhand]

Hey all,

I have a lookup table that stores the IDs from two other tables as my database falls under the "many to many relationship" category.

Here's the create table sql:

$sql = "CREATE TABLE IF NOT EXISTS mylookup (
OID INT(11) UNSIGNED NOT NULL,
LID INT(11) UNSIGNED NOT NULL,
PRIMARY KEY(OID,LID))";

An example of populated data may be:

+-----------+
| OID | LID |
+-----+-----+
| 5 | 1 |
| 1 | 2 |
| 4 | 2 |
| 2 | 2 |
| 3 | 2 |
| 4 | 3 |
| 5 | 3 |
+-----+-----+

My question is how do I go about selecting *ALL* the rows (the OID values) that correspond to a particular "LID" with one MySQL Select Query?

Thus far, the MySQL code I have (see below) is only returning the very first row for the "OID" field...

$sql = @mysql_query("SELECT *,COUNT(LID) as countLID FROM mylookup WHERE LID='$myID'");

Note: I'm using countLID for testing purposes just to see how many results I should get back. The '$nyID' variable is a PHP variable I'm plugging in to get the desired 'LID'.

I've tried using GROUP BY OID and GROUP BY LID and GROUP BY "OID,LID" to no avail.

I'm sure there's a way - but I just haven't figured it out yet.

Any and all comments, insights, or suggestions are welcome.

Thanks in advance!

- Tyhand

 

[r937]

SELECT oid 
  FROM mylookup 
 WHERE lid = $myID -- note no quotes

SELECT oid 
     , COUNT(*) AS rows
  FROM mylookup 
 WHERE lid = $myID -- note no quotes
GROUP
    BY oid

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[tyhand]

Hey R937,

Thanks. First on got it.

Best,
- tyhand