MySQL get all associated records where at least one record matches query

sql at least one record exists
oracle select at least one row
mysql group by return all rows
mysql match all values
sql not exists
sql query for finding records where count(*) > 1
mysql check if all rows have same value
select 1 where exists

I'm trying to get this MySQL search query to work. I want it to return all users where either the firstName, lastName, email, or at least one secondaryEmail match the search string. A user can have many secondary emails.

Here is my SQL query:

SELECT `users`.`id`,
       `users`.`first_name` AS `firstName`,
       `users`.`last_name` AS `lastName`,
       `users`.`email`,
       `usersSecondaryEmails`.`id` AS `usersSecondaryEmails.id`,
       `usersSecondaryEmails`.`email` AS `usersSecondaryEmails.email`
FROM `Users` AS `users`
LEFT OUTER JOIN `UsersSecondaryEmails` AS `usersSecondaryEmails`
        ON `users`.`id` = `usersSecondaryEmails`.`user_id`
WHERE (`users`.`first_name` LIKE '%bob%'
        OR `users`.`last_name` LIKE '%bob%'
        OR `users`.`email` LIKE '%bob%'
        OR `usersSecondaryEmails`.`email` LIKE '%bob%');

This query returns successfully, however when there is only a matching secondary email, it ONLY returns that email, not ALL associated emails. How can I change this query to return ALL emails if it finds at least one secondary email? I know it most likely will have a subquery with EXISTS in it but I'm not too sure where I would put it.

EDIT: Users table has first_name, last_name, and email UsersSecondaryEmails table has user_id and email

If a user has 2 secondary emails (the 'email' column on UsersSecondaryEmails), and at least one of them matches the search string, I expect to get ALL of their secondary emails back. As of right now, this only returns the matching email.

I believe you want:

SELECT u.`id`, u.`first_name` AS `firstName`, u.`last_name` AS `lastName`, u.`email`,
       us.`id` AS `usersSecondaryEmails.id`,
       us.`email` AS `usersSecondaryEmails.email`
FROM `Users` u LEFT OUTER JOIN
     `UsersSecondaryEmails` us
     ON u.`id` = us.`user_id`
WHERE (u.`first_name` LIKE '%bob%' OR u.`last_name` LIKE '%bob%' OR u.`email` LIKE '%bob%') OR
      EXISTS (SELECT 1
              FROM `UsersSecondaryEmails` us2
              WHERE us2.user_id = us.user_id AND us2.`email` LIKE '%bob%'
             );

This assumes you want all second emails even when only the fields in users match. If not, that could be taken into account.

Retrieve all the group records if at least one row matches the , Try this. SELECT * FROM yourTable WHERE merchant_id IN ( SELECT merchant_id FROM yourTable WHERE state LIKE '%FAILED'). To me, it is synonymous to a "row". You seem to aim at "all rows with the same value for the name field" as a definition for a record. At least, I conclude this rereading your original wish: > I just want to pickup the records that matches both the categories "cat1" and "cat2" Well, clearly there are no rows that match both categories at once.

A Subquery should do it

 SELECT 
       `users`.`id`,
       `users`.`first_name` AS `firstName`,
       `users`.`last_name` AS `lastName`,
       `users`.`email`,
       `usersSecondaryEmails`.`id` AS `usersSecondaryEmails.id`,
       `usersSecondaryEmails`.`email` AS `usersSecondaryEmails.email`
    FROM users users, UsersSecondaryEmails usersSecondaryEmails
    WHERE users.id IN (SELECT user_id FROM UsersSecondaryEmails)
    AND users.id = usersSecondaryEmails.user_id

    AND
    (
    `users`.`first_name` LIKE '%bob%'
    OR
    `users`.`last_name` LIKE '%bob%'
    OR
    `users`.`email` LIKE '%bob%'
    )

Select rows for which at least one row per set meets a condition , then that one's picked first Sorting null values after all others, except special stat IS NOT FALSE;. For lots of rows per company_id , one of these techniques may be faster, still: Optimize GROUP BY query to retrieve latest record per user. I'm trying to get this MySQL search query to work. I want it to return all users where either the firstName, lastName, email, or at least one secondaryEmail match the search string. A user can have

You should use a subquery fpor avoid the use of column related to a left join table in where (this produce an inner join)

SELECT `users`.`id`,
       `users`.`first_name` AS `firstName`,
       `users`.`last_name` AS `lastName`,
       `users`.`email`,
       t.`email` AS `usersSecondaryEmails.email`
FROM `Users` AS `users`
LEFT OUTER JOIN (       
    select `usersSecondaryEmails`.`user_id`,  `usersSecondaryEmails`.`email`
    from `UsersSecondaryEmails`
    where `usersSecondaryEmails`.`email` LIKE '%bob%') t on t.`user_id` = `users`.`id 
where (

    `users`.`first_name` LIKE '%bob%'
    OR `users`.`last_name` LIKE '%bob%'
    OR `users`.`email` LIKE '%bob%'
    )

MySQL 8.0 Reference Manual :: 13.2.10 SELECT Statement, where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause. In the WHERE  How do I select data from a table which matches some rows in another table in MySQL? What are you trying match the rows by? Are you trying to match it based upon 2 or 3 columns? or all columns?

SQL: EXCEPT Operator, The SQL EXCEPT operator is used to return all rows in the first SELECT statement Explanation: The EXCEPT query will return the records in the blue shaded area. There must be at least one table listed in the FROM clause. that does not match the customer_id, last_name, and first_name value in the customers table. How to Select Individual Records From MySQL Table Tutorial Learn how to select particular records from a MySQL table As well as showing the whole database table, PHP can be used to select individual records, or records which match certain criteria.

SQL joins and how to use them, SQL handles queries across more than one table through the use of JOINs. We​'ll take a look at each type of JOIN in turn, but first lets go over the general or RIGHT OUTER JOIN that displays all reviews and their associated books, Since it returns all combinations, a CROSS JOIN does not need to match rows using a  // this is where we get all the tables for cross reference to be sure table exists // //-----//

MySQL Tutorial - MySQL By Examples for Beginners, Insert on all Columns INSERT INTO tableName VALUES (column1Value, mysql> CREATE DATABASE southwind; Query OK, 1 row affected (0.03 sec) of the first record to 1001, and use AUTO_INCREMENT for the rest of records by SELECT command can be used to query and join data from two related tables. In addition, rows were ordered by PersonID value and the linked values also were ordered in the same way. In general, the identifiers for persons and the order of the linked values will not have the same order. In addition, all persons with a linked value had one shared with at least one other person.

Comments
  • Could you provide some sample data and expect result?
  • Table structures??? Sample input??? Expected output??? Here's a guide how to ask question. stackoverflow.com/help/how-to-ask
  • This works, but isn't it inefficient to search all emails where the email is like bob and then narrow it down by ones where the user_id matches? It should be the other way around
  • That would be great if you could
  • done. You are correct in your comment. I was not 100% on your original logic. Fixed now.
  • Now it is returning 0 results when a secondary email matches
  • That would lead me to believe that nobody with the '%bob%' criteria has an id in users that matches the user_id in usersSecondaryEmails.
  • I'm getting "unknown column 'usersSecondaryEmails.user_id' in field list" error
  • It didn't fix it. Where is the t variable coming from?
  • t is an alias for the subquery ..anyway you should add a proper data sample and the expected result