Select Data from two tables where ID on both tables are same

Okay I have two tables called subobject: parentID, objectName, subID(primary) and subrelation: ID, className

I want to match SubID with ID from both tables depending if they are the same values, then iterate all the values that are the same. Whats the query to do this in Mysql.

this is how I want it to look:

subobjectNAME: --RelatedClass --RelatedClass2

etc.

I know this is has something to do with JOIN and this is the mysql Query im using but its not working

"SELECT * from subrelation inner join subobject on subrelation.ID = subobject.subID"

also my while loop to grab this

while($join = mysqli_fetch_assoc($join))

Please post the query that you have tried.
– Kermit
May 24 '13 at 15:03

3 Answers
3

JOIN the two tables:

JOIN

SELECT so.objectName, sr.ClassName FROM subobject AS so INNER JOIN subrelation AS sr ON so.subId = sr.ID;

See it in action here:

Also, see the following post for more info about the different types of JOINs:

JOIN

seems to work, do you know what mysqli_fetch_* do I use to iterate this in php?
– Cubatown
May 24 '13 at 15:14

@Cubatown - It will be the same way, just replace the query you tried with this query, but you will be expect two columns' names $row['objectName'] and $row['ClassName'] echo them inside the while loop.
– Mahmoud Gamal
May 24 '13 at 15:16

$row['objectName']

$row['ClassName']

while

Thank you for your solution.
– wick3d
Jan 16 '15 at 15:25

select a.objectName, b.className from subobject a left join subrelation b on a.subID = b.ID

Use a Join

Join

SELECT subobject.ObjectName, subrelation.ClassName FROM subobject INNER JOIN subrelation ON subobject.subID = subrelation.ID

You can find information on SQL Joins here:
http://en.wikipedia.org/wiki/Join_(SQL)

And information from the MySQL manual on Joins:
http://dev.mysql.com/doc/refman/5.0/en/join.html

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

搜尋此網誌

Dfyjkt