Solved

Search two MySql tables in one query

Posted on 2014-02-07
2
498 Views
Last Modified: 2014-04-06
How can I construct a query to search for the same string in 2 tables at once?
0
Comment
Question by:ncw
2 Comments
 
LVL 55

Accepted Solution

by:
Julian Hansen earned 500 total points
ID: 39841679
This is a bit of an open ended question.

Are the tables related

Do you want to find records in table 1 that match a string and the records in table 2 that match that string and display the resulting values in the same row?

Depending on how your data is strucuted and how you want to dispaly the data you could use either a JOIN or a UNION

The JOIN joins to related tables on a field - you can also select values from the combined tables

SELECT * FROM table1, table2 WHERE table1.field = 'value' AND table2.field='value';

Open in new window


This will create all combinations of rows from table 1 and table 2 joined together where records for either table match the criteria

Using a JOIN

SELECT * FROM table1 JOIN table2 on table1.relatedfield = table2.relatedfield WHERE table1.field = 'value'

Open in new window


Differs from the first query in that it will only select records from both tables that have matching values for relatedfield and where field is equal to value. In both cases fields from both tables are returned in a single row

Using a UNION will return the records from one table followed by a new set of rows representing the results from the second table. The restriction is that the same number of fields must be selected for each query in the union

SELECT name, address, email FROM table1 WHERE field='value'
UNION
SELECT name, address, email FROM table2 WHERE field='value'

Open in new window

0
 
LVL 1

Author Comment

by:ncw
ID: 39854727
Sounds like the union might be best. I wanting to effectively search for a string in one table and then the same string in the other table, so to return all results from all tables. Normally the string is only likely to occur in one or the other tables.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question