Solved

Search two MySql tables in one query

Posted on 2014-02-07
2
499 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 56

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

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 This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

685 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