CODEWITHSUNDEEP
mysql
user3101337
user3101337

Reputation: 364

Select value in table if it exists in another

I am trying to get all the values from a users table where a specific company name is found. I also need to get the rows in the users table where that same company name is in another table and matches the name in users table

I have 2 tables like this:

Table: Users.

   USERNAME COMPANY EMAIL PHONE

Table: Dispatchers.

   USERNAME DISPATCH_CLIENT

I need to get all the rows in the Users table that have a specific company name

I also need to get all the rows in the Users table where the USERNAME and DISPATCH_CLIENT fields in the Dispatch table match the USERNAME AND COMPANY field in Users table.

I have looked at some questions on here using EXISTS, but it either didn't work in my situation or i'm not smart enough to understand it.

This is what I have at the moment. 

SELECT * 
    FROM Users
    WHERE Users.COMPANY = '" . $company . "'
    AND EXISTS 
    (SELECT USERNAME
        FROM Dispatchers WHERE DISPATCH_CLIENT = '" . $company . "')";

Also tried doing it this way but no records are returned:

SELECT 
        *
        FROM 
        Users
        INNER JOIN
        Dispatchers
        ON Dispatchers.USERNAME = Users.USERNAME
        AND Dispatchers.DISPATCH_CLIENT = Users.COMPANY
        WHERE 
        Users.COMPANY = '" . $company . "'"

Upvotes: 0

Views: 58

Answers (2)

Chameera Dulanga
Chameera Dulanga

Reputation: 228

Try this

SELECT * 
    FROM Users
    WHERE Users.COMPANY = '" . $company . "'
    OR Users.USERNAME IN
    (SELECT USERNAME
        FROM Dispatchers WHERE DISPATCH_CLIENT = '" . $company . "')";

Upvotes: 1

momo
momo

Reputation: 141

I think you missed the join condition in "exists" phase. Please try this.

SELECT * 
    FROM Users
    WHERE Users.COMPANY = '" . $company . "'
    AND EXISTS 
    (SELECT USERNAME
        FROM Dispatchers 
        WHERE Dispatchers.DISPATCH_CLIENT = Users.COMPANY 
             and Users.USERNAME = Dispatchers.USERNAME)

Upvotes: 0

Related Questions