CODEWITHSUNDEEP
phpmysqlsql
tradebel123
tradebel123

Reputation: 435

How to SELECT records from One table If Matching Record In Not Found In Other Table

Halo i am trying for a query to select record from one table say 'deal_asking' only if a matching record is not found in the second table 'deal_unpluged'. this what i does (below) selecting record using LEFT JOIN and then filter the record in PHP side. What i am looking for an Mysql query way of solution for this problem please help..

thanks in advance...

SELECT DA.das_id, DU.das_id_fk 
FROM deal_asking DA 
LEFT JOIN deal_unpluged DU ON DA.das_id= DU.das_id_fk 
WHERE department='8'
ORDER BY das_id ASC LIMIT 10 OFFSET 0

Upvotes: 0

Views: 82

Answers (1)

Sean Bright
Sean Bright

Reputation: 120714

Just add this to your WHERE clause:

AND DU.das_id_fk IS NULL

Say I have the following two tables:

+-------------------------+   +-------------------------+
| Person                  |   | Pet                     |
+----------+--------------+   +-------------------------+
| PersonID | INT(11)      |   | PetID    | INT(11)      |
| Name     | VARCHAR(255) |   | PersonID | INT(11)      |
+----------+--------------+   | Name     | VARCHAR(255) |
                              +----------+--------------+

And my tables contain the following data:

+------------------------+    +---------------------------+
| Person                 |    | Pet                       |
+----------+-------------+    +-------+----------+--------+
| PersonID | Name        |    | PetID | PersonID | Name   |
+----------+-------------+    +-------+----------+--------+
| 1        | Sean        |    | 5     | 1        | Lucy   |
| 2        | Javier      |    | 6     | 1        | Cooper |
| 3        | tradebel123 |    | 7     | 2        | Fluffy |
+----------+-------------+    +-------+----------+--------+

Now, if I want a list of all Persons:

SELECT pr.PersonID, pr.Name
FROM
    Person pr

If I want a list of Persons that have pets (including their pet's names):

SELECT pr.PersonID, pr.Name, pt.Name AS PetName
FROM
    Person pr
    INNER JOIN Pet pt ON pr.PersonID = pt.PersonID

If I want a list of Persons that have no pets:

SELECT pr.PersonID, pr.`Name`
FROM
    Person pr
    LEFT JOIN Pet pt ON pr.PersonID = pt.PersonID
WHERE
    pt.`PetID` IS NULL

If I want a list of all Persons and their pets (even if they don't have pets):

SELECT
    pr.PersonID,
    pr.Name,
    COALESCE(pt.Name, '<No Pet>') AS PetName
FROM
    Person pr
    LEFT JOIN Pet pt ON pr.PersonID = pt.PersonID

If I want a list of Persons and a count of how many pets they have:

SELECT pr.PersonID, pr.Name, COUNT(pt.PetID) AS NumPets
FROM
    Person pr
    LEFT JOIN Pet pt ON pr.PersonID = pt.PersonID
GROUP BY
    pr.PersonID, pr.Name

Same as above, but don't show Persons with 0 pets:

SELECT pr.PersonID, pr.Name, COUNT(pt.PetID) AS NumPets
FROM
    Person pr
    LEFT JOIN Pet pt ON pr.PersonID = pt.PersonID
GROUP BY
    pr.PersonID, pr.Name
HAVING COUNT(pt.PetID) > 0

Upvotes: 4

Related Questions