Solved

Search two MySql tables in one query

Posted on 2014-02-07
2
495 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 51

Accepted Solution

by:
Julian Hansen earned 500 total points
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sample Mysql database DATA needed 3 46
php survey script 4 57
SQL Column not found 7 42
simple sql statement 3 46
A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now