[SOLVED] Multiple select advice

[JonasE]

Hello,

 

I currently have two tables:

 

documents with fields:

id

title

 

vartitles with fields:

id

doc_id

vartitle

 

Each record can have one primary title (record.title) and several vartitles (referenced by record_id)

 

Here's my problem: I want to search these two tables for matches to titles. After this, I want to return all the documents matching the the title (i.e. matching vartitle and title fields) regardless of whether match for the title was found in the documents table, or in the vartitles table. I know that I can use UNION ALL to return for instance documents.id and vartitles.record_id fields, but how do I select all records matching either of those? Do i use a subquery of some sort?

 

Thank you,

 

Jonas

[artacus]

Use a left join and an OR in your where clause.

[JonasE]

Thanks for the reply Artacus.

 

Do I need to do a UNION at all? Could you provide an example?

 

Thanks so much.

[artacus]

No union. I'm not sure if you've got a full text index or not. Use match() against() if you do.

 
SELECT d.*
FROM documents AS d
LEFT JOIN vartitles AS v ON d.id = v.doc_id
WHERE d.title LIKE '%search%' OR v.vartitle LIKE '%search%'
GROUP BY d.id

[JonasE]

Thanks so much Artacus. I'll give that a go!