Help with a query!

Ego

Greetings!

I have a query that, if somebody has some time, i could use help with. Here we start:


 article table
 ---------------

  articleid int(255) PRI NULL autoincrement
  threadid int(255) NULL  0
  posted datetime  0000-00-00 00:00:00
  topic varchar(255) 
  post text
  views int(255)
  replies int(255)
  memberid int(255)
  forum varchar(255)

 reply table
---------------
  replyid int(255) PRI NULL autoincrement
  threadid int(255) MUL 0
  reply text
  memberid int(255) 0

Ok, those are the tables i need data out of. What I am looking for is a query that says "If a user searches for a string, i want to look in both article.post and reply.reply. If the string is in either, return the results.

Here is a sample dataset from both tables:

mysql> select post from article;
+-------------------+ 
| post                     |
+-------------------+
| Posted article 3    |
| Posted Article #2 |
| post test              |
+-------------------+
3 rows in set (0.00 sec)


mysql> select reply from reply
    -> ;
+---------------------------------+
| reply                                            |
+---------------------------------+
| One reply from the cheap seats! |
| This is a reply                               |
| This is a second reply                   |
| This is a third reply                       |
| One Reply                                     |
| 2 replies                                        |
+---------------------------------+
6 rows in set (0.00 sec)

Here is the query I thought might work:

select article.post,reply.reply from article,reply where article.post LIKE "%This%" or reply.reply LIKE "%This%";

However, this post gets me these results:

mysql> select article.post,reply.reply from article,reply where article.post LIKE "%This%" or reply.reply LIKE "%This%";
+-------------------+------------------------+
| post              | reply                  |
+-------------------+------------------------+
| Posted article 3   | This is a reply               |
| Posted Article #2 | This is a reply              |
| post test              | This is a reply              |
| Posted article 3    | This is a second reply |
| Posted Article #2  | This is a second reply |
| post test               | This is a second reply |
| Posted article 3     | This is a third reply     |
| Posted Article #2   | This is a third reply     |
| post test                | This is a third reply     |
+-------------------+------------------------+
9 rows in set (0.01 sec)

For some reason, this gets me data that is repeated. All i want is a single list of results that give me exactly whats in both tables...no repeats.

Can anybody help me with a query like that? Or...at least explain to me why Im getting results like this?

Thank You for help.

paulnaj

Try ...

SELECT article.post, reply.reply
FROM article
INNER JOIN reply USING (threadid)
WHERE article.post LIKE "%This%" OR reply.reply LIKE "%This%"

Paul 🙂

Ego

Thank you very much for the help. Unfortunately, this query didn't work either.

Here is the query:

SELECT article.post, reply.reply 
FROM article
INNER JOIN reply USING (threadid) 
WHERE article.post LIKE "%This%" OR reply.reply LIKE "%This%"

Here is the results:

+-----------+------------------------+
| post      | reply                  |
+-----------+------------------------+
| post test | This is a reply        |
| post test | This is a second reply |
| post test | This is a third reply  |
+-----------+------------------------+
3 rows in set (0.01 sec)

Which is not quite what i want. I would like each record from the post table matching LIKE "%this%" and each record from the reply table mathing "%reply%"

If this is possible. If it isn't, then Ill just use 2 selects and a hash.

Thank you for help.