Can I use sub queries and a join in the same statement in MYSQL

Question

This problem has left me clueless. I am trying to use a Join in mysql and a subquery and I keep getting a syntax error.

The statement in question is

SELECT Customer.customer_id, Customer.name, Order.address FROM Customer 
WHERE customer_id = (SELECT customer_id FROM Order WHERE customer_id = "625060836f7496e9fce3bbc6") 
INNER JOIN Order ON Customer.customer_id=Order.customer_id;

I have tried to just use the query without the Subquery and it works fine.

SELECT Customer.customer_id, Customer.name, Order.address FROM Customer  
INNER JOIN Order ON Customer.customer_id=Order.customer_id;

Removing the join but keeping the subquery also works.

SELECT Customer.customer_id, Customer.name, Order.address FROM Customer 
WHERE customer_id = (SELECT customer_id FROM Order WHERE customer_id = "625060836f7496e9fce3bbc6")

Only using both the subquery and the join results in a syntax error

I cannot seem to find the error.

What have I done wrong here.

Thanks in advance

Answer 1

The secret is to get the syntax right!

When querying more than one table it's good practice to use aliases to reference them and where multiple tables share the same column names, also string literals should be delimited with single 'quotes'.

In this specific example however the subquery is superfluous, just use the string literal directly in the where clause.

SELECT c.customer_id, c.name, o.address 
FROM Customer c
JOIN Order o ON c.customer_id = o.customer_id
WHERE c.customer_id = (
  SELECT customer_id 
  FROM Order 
  WHERE customer_id = '625060836f7496e9fce3bbc6'
);