[RESOLVED] How to get Parent record when both Child and Parent are in the same table?

[partypete]

Hey all,

Okay I think my brain was asleep at the wheel when I did this. I made a small forum and because it's small I went and put both topics(parent records) and posts(child records) in the same table. My problem is that when a search is performed and a match is found in a post, I don't know how to then get its parent(topic) so that a page of topic titles can be displayed.

So my table looks something like this:

id     topic_title     post_topic_id     post_message

1      cars            0
2                      1                 I love cars
3                      1                 Ford or nothin!
4                      1                 Cars, women... trouble!
5      vacations       0
6                      5                 Hawaii is nice.
7                      5                 Cancun has the ladies.
8                      5                 O' Canada!

Keeping in mind that I want only topic rows(the ones with a title) returned... If someone searches for "cars", no problem, as the cars is found in the one topic title and that topic will be returned. But if someone searches for "Canada", a post will be found but not its topic.

So essentially, if the search word is found in the topic title, great, get that row!
Else if the search word is found in the post, forget about it BUT get the topic row that corresponds to its post_topic_id. So, if "Canada" was found in a post with the id 8, we really want to return its topic row wich is id 5.

Any idea how to do this?

Again, my apologies. I don't know why I did it this way. lol I should have put topics and posts in separate tables and joined them together.

Thanks as always!
Peter

[bradgrafelman]

Sounds like you could use a subquery with an IF() statement to select the right post ID (either "id" or "post_topic_id")... example SQL:

SELECT id, topic_title
FROM posts
WHERE id =
(
    SELECT IF(post_topic_id = 0, id, post_topic_id)
    FROM posts
    WHERE <search condition here>
)

[partypete]

Thanks for your help so far Brad(?),

What you wrote seems to be close to what I need except I'm getting this error;

[Err] 1242 - Subquery returns more than 1 row

First I tried just as you suggested:

SELECT id, topic_title
FROM posts
WHERE id =
(
    SELECT IF(post_topic_id = 0, id, post_topic_id)
    FROM posts
    WHERE topic_title LIKE '%search%' OR post_message LIKE '%search%'
)

I got the above error message because the subquery found more than one post for the topic. So I modified it by adding a GROUP BY:

SELECT id, topic_title
FROM posts
WHERE id =
(
    SELECT IF(post_topic_id = 0, id, post_topic_id) AS topic_id
    FROM posts
    WHERE topic_title LIKE '%search%' OR post_message LIKE '%search%'
    GROUP BY topic_id
)

This worked as long as the search only found posts in a single topic. But if the search finds posts belonging to several topics... I again get the error message.

Any iders?

Thanks!

[partypete]

I guess when looking at this, it will only ever return one row(topic)

WHERE id =
(

My goal is to of course return all topics who's title or posts match the users search, like any forum.

Hmmmm... what to do, what to do...

[bradgrafelman]

Woops, wasn't thinking clearly. Simply replace the equal sign before the subquery with the word 'IN' and it'll handle multiple matches properly.

[partypete]

Thanks, that did the trick! :-)

Using IN, IF, and a subquery is pretty much duplicating what my previous patchwork solution was doing... only now it's done in 1 mysql step instead of 3 or 4 mysql/php steps! lol

Thanks again!

Peter

And for anyone down the road with this question, this is the working solution:

SELECT id, topic_title
FROM posts
WHERE id IN
(
    SELECT IF(post_topic_id = 0, id, post_topic_id)
    FROM posts
    WHERE topic_title LIKE '%search%' OR post_message LIKE '%search%'
)

[partypete]

I just noticed that the query in my previous post produces an error if no results are found in the subquery. If the subquery can't find any records, then it produces no values for IN() which mysql can't handle.

The error:
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 9"

If the subquery can't find any results, I need it to return some value for IN()... a value that will obviously cause the main query to find no records aswell... 0 (zero) would work in this case.

Any ideas?

Thanks!
Peter

[partypete]

Okay weird.... The query in my webpage causes the error, but if I echo the query to the page and then copy and paste it into Navicat, the query does not return an error... just no result as expected.

Why would an identical query fail in one place(webpage) and not another(Navicat, mysql software)??

Hmmmmmmm.....

[bradgrafelman]

Can you echo out the entire query string as well as the error number when you get that error message?

I would think that MySQL can gracefully handle a subquery returning no results for an IN() statement.. but perhaps I'm wrong.

EDIT: I've also unmarked this thread as 'resolved' since it would appear that it actually isn't.

[partypete]

Thanks Brad for marking this unresolved. I searched how to do this myself, but I guess it's not an option... but should be. :-)

Anyway... this is exactly what is printed to the screen after a search that returns no result. There appears to be no mysql error number:

SELECT f_id, f_title, f_author, f_replies, f_active, f_pinned, user_firstname, user_lastname FROM forum LEFT JOIN users ON forum.f_author=users.user_id WHERE f_id IN ( SELECT IF(f_parentid = 0, f_id, f_parentid) FROM forum WHERE f_title LIKE '%notgonnafindthis%' OR f_message LIKE '%notgonnafindthis%' ) ORDER BY f_pinned DESC, f_active DESC You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 9

Cleaned Up(similar to how it's written in my pages source code):

SELECT
 f_id, f_title,
 f_author,
 f_replies,
 f_active,
 f_pinned,
 user_firstname,
 user_lastname
FROM
 forum
LEFT JOIN
 users
ON
 forum.f_author=users.user_id
WHERE
 f_id IN
 (
  SELECT
   IF(f_parentid = 0, f_id, f_parentid)
  FROM
   forum
  WHERE
   f_title LIKE '%notgonnafindthis%'
  OR
   f_message LIKE '%notgonnafindthis%' 
 )
ORDER BY
 f_pinned DESC,
 f_active DESC

(Produces:)
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 9

[bradgrafelman]

[man]mysql_errno/man will give you the error number.

Also, exactly how does it appear in your source code? The error talks about a ')' being out of place, but mentions it being on line #9. According to you, line #9 is "FROM" with no ')' in sight until much later...?

[partypete]

You are correct Brad, the closing ) wasn't at line 9... in that query. lol

Sorry about that, the error was in a second query that depends on this query. The results from this query are used to generate values for IN() in the second query, so when this query failed to find any records, the IN() in the second query had no values. I've changed my script so if the values for IN() are empty, a default value is entered to prevent errors.

Thanks again though! I'm off to hang my head in shame for wasting your time.... ;-)

Peter