CODEWITHSUNDEEP
mysqlworkbench
xTuckii
xTuckii

Reputation: 37

Joining two tables using subquery in mysql

Im trying to write a SELECT statement with a subquery that determines the customer_id and the last_name of the customer whose credit card expires soonest as shown in my orders table and for this i want to use a WHERE clause but im stuck since the result only displays one customer... here is what i have so far. if someone could guide me in the right path..

SELECT customer_id, last_name
FROM customers
WHERE NOT EXISTS
(SELECT card_expires
FROM orders
WHERE customers.customer_id = orders.customer_id
);

Upvotes: 0

Views: 10444

Answers (2)

Michael Blood
Michael Blood

Reputation: 1267

While I am not clear on your exact filtering or ordering requirements, I think you may want to start with the following.

SELECT customer_id, last_name, card_expires
FROM customers
  left join ( 
      SELECT card_expires
      FROM orders
  ) o on (customers.customer_id = orders.customer_id)

Using this method you can see a full list of all customers and card_expires, and THEN you can start to filter the list down to what you want. You might build up the subquery on orders to select a specific subset of orders. Again I am not clear on what you are trying to do exactly. But by selecting all of the information first you will be able to see all of the information and be able to decide what to filter out.

You may decide you would ALSO like to see more columns from the order table to help you decide which orders to include / exclude.

If you wanted to only see a list of customers that do not have a card_expires entry

 SELECT customer_id, last_name, card_expires
    FROM customers
      left join ( 
          SELECT card_expires
          FROM orders
      ) o on (customers.customer_id = orders.customer_id)
    where o.card_expires is null

If you want to see only customers WITHOUT a card that expires in the future.

     SELECT customer_id, last_name, card_expires
        FROM customers
          left join ( 
              SELECT card_expires
              FROM orders
              where card_expires > now()
          ) o on (customers.customer_id = orders.customer_id)
        where o.card_expires is null

When crafting an SQL statement, something that can make it easier to do is FIRST create an SQL statement which returns ALL of the columns you will use to filter the list by, THEN apply the filter. This will allow you to more easily add and remove criteria until you have it right.

I like to split each criteria on to its own separate line so that I can easily comment lines out with two dashes. EX:

 SELECT customer_id, last_name, card_expires
    FROM customers
      left join ( 
          SELECT card_expires
          FROM orders
          where card_expires > now()
      ) o on (customers.customer_id = orders.customer_id)
    where 0=0
      and o.card_expires is null
      -- and last_name = 'Smith'

Upvotes: 2

Boy
Boy

Reputation: 612

Use LEFT JOIN/INNER JOIN, i assume you want ORDER BY ASC, since you want the soonest

SELECT customer_id, last_name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY card_expires ASC;

Upvotes: 0

Related Questions