By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,652 Members | 1,337 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,652 IT Pros & Developers. It's quick & easy.

Searching 2 tables at once

P: 3
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.
Aug 16 '07 #1
Share this Question
Share on Google+
4 Replies


code green
Expert 100+
P: 1,726
You have not specified a search criteria, but you could JOIN the tables.
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM `news_item`,`site_content`
  2. WHERE `list_of_fields_to_search` LIKE '%searchstring%'
Aug 16 '07 #2

P: 57
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.
Aug 17 '07 #3

P: 57
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM `news_item`,`site_content`
  2. WHERE `list_of_fields_to_search` LIKE '%searchstring%'
Aug 17 '07 #4

P: 57
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.
Aug 17 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.