Topic: I need help to build a query that selects from two tables

[simplyi]

Hello I need a help to build a query that selects from two tables. It seems like I do not get expected results when I use JOIN..
I have to tables:

Table:
books
id int
title varchar
description text

And table
tags
book_id int
tag varchar

I need to build a query that will performs a SELECT using a KEYWORD and selects a distinct books.tilte where books.title like %keyword% AND tags.tag like %keyword%. Two tables need to be searched but the result should be DISTINCT books.title, books.id, books.description.
When I run a query using JOIN I get results from both of the tables and I results are duplicating...
Or when I run a query like this

select distinct q.title, q.id from `books` q, tags t where (q.title like "%keyword%") or ( t.tag like "%keyword%")
and there are no results that match q.title I STILL GET rows from table books selected. 

Thank you very much for your advice!

[kickstart]

Hi

Reason you last query is bringing back rows is because it is doing a CROSS JOIN, and bring back every combination of rows from the books and tags tables, then discarding those which have neither a title or tag matching your keywords.

Try something like this

Code: [Select]

SELECT distinct q.title, q.id
FROM books q
INNER JOIN tags t
ON q.id = t.book_id
WHERE q.title like '%keyword%'
OR  t.tag like '%keyword%'

All the best

Keith

[simplyi]

Keith, thank you very much for your response.

The query that you advised me works and selects distinct records. But it works only in those cases when t.book is in the Tags table. In my case it is possible that User posts a book and does not provide any tags for it. So there will be a new record in the Books table and no record in the Tags table. So in this situation if I search for book title nothing is going to be found even when searched book title is in Book table.

The result I need to achieve is - If keyword matches Book title then query returns a record. AND if keyword is found in the Tags table, query returns a record. But records need to be distinct. No two same book record can be returned.

Can you advise me how to fix this?

Thank you!

[kickstart]

Hi

If you use a LEFT OUTER JOIN rather than an INNER JOIN then I think it will give you what you want.

All the best

Keith

[simplyi]

kickstart! Works! you are the man!

Thank you!