Solved

Search two MySql tables in one query

Posted on 2014-02-07
2
497 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 54

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

777 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