Solved

Search two MySql tables in one query

Posted on 2014-02-07
2
500 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 57

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

Title # Comments Views Activity
mysql database, schema and table creation 13 114
Present Absent from working date rage 11 56
Duplicated data in GROUP_CONCAT 2 50
Mysql query one to many 11 38
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…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

732 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