Help with MySQL JOIN statement

Question

I have 2 tables:

Table A is a category table. Columns are cid, catname.

Table B is a relationship table. Columns are cid, parent (parent is another cid).

Here's where I am so far:

"SELECT c.cid, c.catname AS catname, r.parent AS parent FROM tableA AS c JOIN tableB AS r ON r.cid=c.cid";

I know I'll get 3 columns (2 from tableA and one from tableB) but I also want to get the catname value from the parent in tableA If I were to do a second query, it would look like this (assuming we put the result into a $row variable):

"SELECT catname FROM tableA WHERE cid='".$row['parent']."'";

That way I can display it as text.

What do I add, and where? Is there a second JOIN?

Kerrek SB · Accepted Answer · 2011-08-18 03:35:05Z

2

You can join a table multiple times:

SELECT a.cid AS acid, a.catname AS aname,
       b.cid AS bcid, b.catname AS bname
FROM relationships AS r
     JOIN categories AS a ON (r.cid = a.cid)
     JOIN categories AS b ON (r.parent = b.cid)

answered Aug 18, 2011 at 3:35

Kerrek SB

480k96 gold badges904 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stephan Hovnanian Over a year ago

thank you! I can't believe I actually understand how you did that. I must be learning something :)

Collectives™ on Stack Overflow

Help with MySQL JOIN statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related