Searching 2 tables at once

[dodjem]

Hi all,

I am facing an issue which I really don't know how to solve after googling for quite some time.

I have 2 tables: one is for my new articles and one is for the website content; I would like to build a search feature which would search both tables and return results from both. I considered using a union but there's a slight problem in that from the site content table I want information from one extra field...

site_content table fields:
ContentID INT(10),
Title VARCHAR(50),
Content TEXT,
Alias VARCHAR(30)

news_items table fields:
NewsID INT(10),
Title VARCHAR(50),
Content TEXT

Any ideas? It would be nice to order the results by relevance score (probably by full text searching) but that's not essential.

[code green]

You have not specified a search criteria, but you could JOIN the tables.

SELECT * FROM `news_item`,`site_content`
WHERE `list_of_fields_to_search` LIKE '%searchstring%'

[DThreadgill]

I've been trying this as well...the Union doesn't work as I have 8 records in one table and 1 in the other that should return 9 results...Only getting 8 from the Union. Any guidance would kill 2 birds (myself & dodjem) with one stone! :)

Hi all,

I am facing an issue which I really don't know how to solve after googling for quite some time.

I have 2 tables: one is for my new articles and one is for the website content; I would like to build a search feature which would search both tables and return results from both. I considered using a union but there's a slight problem in that from the site content table I want information from one extra field...

site_content table fields:
ContentID INT(10),
Title VARCHAR(50),
Content TEXT,
Alias VARCHAR(30)

news_items table fields:
NewsID INT(10),
Title VARCHAR(50),
Content TEXT

Any ideas? It would be nice to order the results by relevance score (probably by full text searching) but that's not essential.

[DThreadgill]

Sorry code green - I guess you were posting this as I was posting mine. I did use a search criteria in my Union and it didn't retrieve the records it should have. Can't use a join because I'm wanting records from both tables and even though the fields are the same names, there are different records in each table.

You have not specified a search criteria, but you could JOIN the tables.

SELECT * FROM `news_item`,`site_content`
WHERE `list_of_fields_to_search` LIKE '%searchstring%'

[DThreadgill]

I was using UNION ALL instead of just UNION....got the records I needed.

Thanks!!

Sorry code green - I guess you were posting this as I was posting mine. I did use a search criteria in my Union and it didn't retrieve the records it should have. Can't use a join because I'm wanting records from both tables and even though the fields are the same names, there are different records in each table.