Using a Query To Intersect another Table

[mofolo]

Hey Guys,

 

I need to intersect a table using the values from a query..

 

For Example:

 

$myData = mysql_query("SELECT theID FROM myData WHERE myID = '$userID'");

 

Now I need to use the Multiple Results returned into the Query $myData to look for data in another table.

 

For Example:

mysql_query("SELECT * FROM otherTable WHERE personID = '$myData'")

 

(that Query Will not work...)

 

So what I am trying to do is return the list of 'theID' from the table 'myData'. Then with the list of IDs, get all the related rows from 'otherTable'.

 

Whats the most effective and efficient way of getting it using mySQL?

 

Thanks.

[fenway]

It's called a JOIN.

[mofolo]

I've look at some Tutorials about Join, but they're confusing.

 

I tried this with no luck.

 

"SELECT theID FROM myData JOIN otherTable ON myData.id = otherTable.id WHERE personID = '$id'"

 

But it only Returns and Joins the first row.

[fenway]

I'm not sure I follow -- (a) you're only asking for "theID" -- don't know what it is -- and (b) check how many matching records actually exist.

[aebstract]

$myData = mysql_query("SELECT theID FROM myData WHERE myID = '$userID'");

mysql_query("SELECT * FROM otherTable WHERE personID = '$myData'")

 

So what I am trying to do is return the list of 'theID' from the table 'myData'. Then with the list of IDs, get all the related rows from 'otherTable'.

 

You're wanting to grab your first result and join your other table based on the id equalling the personID. I think I got what you want right here, not sure if the table-data matches to what you're doing but hopefully 

 

$query = mysql_query("
SELECT *
FROM myData
INNER JOIN otherTable ON myData.myID = personID.otherTable
WHERE myID.myData = '$userID'
");

[mofolo]

That did it!

 

Thanks for the help