Mysql query two tables join conditional

Question

I have two tables, ready_for_client and ready_to_ship. The structures for both tables are almost the same.

    id (int)
    client_id (int)
    truck_id (int)
    driver_id (int)
    list (varchar 50)
    is_done (tinyint 1)

ready_for_client has rts_id (varchar 32) and ready_to_ship has rfc_id (varchar 32)

What I want is three different queries, the first to query both tables where the rts_id and rfc_id is the same. I have the first one and it works.

    SELECT rfc.id AS Ready_For_Client_id, rfc.rts_id, t.Truck_Number, u.Name AS User_Name, c.Name AS Client_Name FROM ready_for_client rfc
      INNER JOIN trucks t ON rfc.truck_id = t.id
      INNER JOIN users u ON rfc.driver_id = u.id
      INNER JOIN clients c ON rfc.client_id = c.id
      INNER JOIN ready_to_ship rts ON rfc.rts_id = rts.rfc_id
    WHERE rfc.is_done = 0

This produces one record just like it should. The second and third queries I would like to select all other records for both tables respectively that do not share the rfc_id and rts_id.

I thought about having the same query but on the fourth INNER JOIN have it not equal to.

    SELECT rfc.id AS Ready_For_Client_id, rfc.rts_id, t.Truck_Number, u.Name AS User_Name, c.Name AS Client_Name FROM ready_for_client rfc
      INNER JOIN trucks t ON rfc.truck_id = t.id
      INNER JOIN users u ON rfc.driver_id = u.id
      INNER JOIN clients c ON rfc.client_id = c.id
      INNER JOIN ready_to_ship rts ON rfc.rts_id != rts.rfc_id
    WHERE rfc.is_done = 0

But this is doubling the the records plus also including the the one record that has the shared rts_id and rfc_id.

Does anyone know how I would set this second and third query to do what I need?

-- Edit --

ready_for_client

    +----+-----------+----------+-----------+---------------------------+----------------------------------+---------+
    | id | client_id | truck_id | driver_id | list                      | rts_id                           | is_done |
    +----+-----------+----------+-----------+---------------------------+----------------------------------+---------+
    |  1 |         1 |        2 |         7 | 175                       | 2bcd069351002c4d92ba151ae67212ad |       0 |
    |  2 |         2 |        2 |         7 | 231                       | 95e906dec3c83b1c9bfbe3f0d6920aaf |       0 |
    |  3 |         2 |        1 |         7 | 88,89,90,91               | e82ac070985e21ced4fb203c515b1618 |       0 |
    |  4 |         3 |        1 |         7 | 37,249,38,39,40,41,109,42 | d21b5ac4a4da0e57b85a464c3a6fd511 |       0 |
    +----+-----------+----------+-----------+---------------------------+----------------------------------+---------+

ready_to_ship

    +----+-----------+----------+-----------+-------+----------------------------------+---------+
    | id | client_id | truck_id | driver_id | list  | rfc_id                           | is_done |
    +----+-----------+----------+-----------+-------+----------------------------------+---------+
    |  1 |         3 |        1 |         7 | 1     | 10e938e49a67117273683535cebbe8cc |       0 |
    |  2 |         3 |        1 |         7 | 2,3,4 | d21b5ac4a4da0e57b85a464c3a6fd511 |       0 |
    +----+-----------+----------+-----------+-------+----------------------------------+---------+

Output for the second query, trying to select all other records from ready_for_client:

    +---------------------+----------------------------------+--------------+------------+----------------------+
    | Ready_For_Client_id | rts_id                           | Truck_Number | User_Name  | Client_Name          |
    +---------------------+----------------------------------+--------------+------------+----------------------+
    |                   1 | 2bcd069351002c4d92ba151ae67212ad |           20 | driver     | client name          |
    |                   1 | 2bcd069351002c4d92ba151ae67212ad |           20 | driver     | client name          |
    |                   2 | 95e906dec3c83b1c9bfbe3f0d6920aaf |           20 | driver     | client name          |
    |                   2 | 95e906dec3c83b1c9bfbe3f0d6920aaf |           20 | driver     | client name          |
    |                   3 | e82ac070985e21ced4fb203c515b1618 |           10 | driver     | client name          |
    |                   3 | e82ac070985e21ced4fb203c515b1618 |           10 | driver     | client name          |
    |                   4 | d21b5ac4a4da0e57b85a464c3a6fd511 |           10 | driver     | client name          |
    +---------------------+----------------------------------+--------------+------------+----------------------+

It should only be for Read_For_Client_id 1 2 and 3. No id 4 and no doubling of 1 2 and 3.

Image if you can that I asked you to produce 3 queries for tables you have never seen.... always provide "sample data" and "expected result" *(as reusable text or sql inserts)* as this gets you accurate answers quickly. — Paul Maxwell, Sep 28 '17 at 05:52
you can use where condition like `where rfc.rts_id != rts.rfc_id` may be that will give you your solution — Jenish, Sep 28 '17 at 05:58
Sorry, didn't even think of that. I have added some data and the result of the second query. — Steven Kapaun, Sep 28 '17 at 06:11

score 1 · Accepted Answer · answered Sep 28 '17 at 06:03

You want to locate the rows where the ids are not equal but you need to avoid the multiplication of rows. Here are 2 techniques for that.

Use a LEFT JOIN but then exclude those where the join exists

SELECT
      rfc.id AS Ready_For_Client_id
    , rfc.rts_id
    , t.Truck_Number
    , u.Name AS User_Name
    , c.Name AS Client_Name
FROM ready_for_client rfc
INNER JOIN trucks t ON rfc.truck_id = t.id
INNER JOIN users u ON rfc.driver_id = u.id
INNER JOIN clients c ON rfc.client_id = c.id
LEFT JOIN (
      SELECT
            rfc.id
      FROM ready_for_client rfc
      INNER JOIN ready_to_ship rts ON rfc.rts_id = rts.rfc_id
      WHERE rfc.is_done = 0
      ) d ON rfc.rts_id = d.id
WHERE d.id IS NULL
;

Use NOT EXISTS. Identify if the current row has a match in the other table, if it does ignore that row.

SELECT
      rfc.id AS Ready_For_Client_id
    , rfc.rts_id
    , t.Truck_Number
    , u.Name AS User_Name
    , c.Name AS Client_Name
FROM ready_for_client rfc
INNER JOIN trucks t ON rfc.truck_id = t.id
INNER JOIN users u ON rfc.driver_id = u.id
INNER JOIN clients c ON rfc.client_id = c.id
WHERE rfc.is_done = 0
AND NOT EXISTS (
      SELECT NULL
      FROM ready_to_ship rts 
      WHERE rfc.rts_id = rts.rfc_id
      ) 
;

Perfect, thank you. The LEFT JOIN still was adding the ready_for_client id 4 (d21b5ac4a4da0e57b85a464c3a6fd511). But your second example NOT EXISTS works. I've also tried it the other way, selecting from ready_to_ship and it works! — Steven Kapaun, Sep 28 '17 at 06:31

Mysql query two tables join conditional

1 Answers1