tricky query, need help!

doozerdc

The db I am building is used to populate a site with navigation menus and content.

So, I have three tables as such: content, leftNav and rightNav:

Here are the tables with their columns:

content
content_id
LNav_id
RNav_id
content

leftNav
LLink_id (unique id for each row)
LNav_id (which subnav menu the item is part of)
LLink (text for display purposes)

rightNav
RLink_id (unique id for each row)
RNav_id (which subnav menu the item is part of)
RLink (text)

The following query is what I've been able to come up with so far.(I'm using MySQL version 4.0.27)

SELECT *, (SELECT content_id FROM content AS c2 WHERE c2.RNav_id = r.RNav_id) AS new_content_id FROM content AS c, leftNav as l,rightNav AS r WHERE c.RNav_id = r.RNav_id AND c.LNav_id=l.LNav_id AND c.content_id = '2'

The subquery is simply to get the content_id for each unqie subnav item so it can be written in the html link (the id is needed to point to the content)

The problem is that the version of MySQL that I have available to me doesn't support subqueries so I need another way to retrieve all this info.

To clarify, here's what the query needs to do:

Get the CONTENT with the ID of '2' and also get all LEFTNAV and RIGHTNAV items that go with that content item. Also, get the content id's for each nav item (in order to include them in the links to be written).

tomhath

I don't see why you need the subquery. You have aready joined "content" to "rightNav" in the main query. Why join to it again?

bpat1434

Take the subquery out, and use separate queries..... 🙁

or update mySQL 🙂

doozerdc

tomhath wrote:
I don't see why you need the subquery. You have aready joined "content" to "rightNav" in the main query. Why join to it again?

I thought I needed the subquery. I'm not sure how I can get all the information I need all in one SELECT statement. If I take out the subquery I DO NOT get the content_id's for each respective RLink_id.

If you can think of a way to do this please tell me how. I need the help.

bpat1434

Couldn't this get you the same information?

SELECT c.*, c.content_id AS new_content_id
FROM content AS c
LEFT JOIN leftNav AS l
	USING(LNav_id)
LEFT JOIN rightNav AS r
	USING(RNav_id)
WHERE c.content_id='2'

Test it out and see....