MyQL JOIN query

I am trying to write a query that joins two tables together
The first table is called customers and the info I need from that table is customerid, name and email
The second table is called orders and it also contains the customerid. This table also contains a column called order_status and this holds the key to the query.
In laymen’s language the query should be:
I need customerid, name, email from table customers (join table customers on table orders using customerid as the joiner) where order_status  is equal to “FULL”

I have tried this myself and come up with:
SELECT  customerid, name, email
FROM customers  JOIN orders ON customers. customerid = customers.customerid
WHERE order_status = FULL
But this does not work
Assistance much appreciated.
N
LVL 5
Neil_BradleyWeb UX/UI DeveloperAsked:
Who is Participating?
 
p_nutsConnect With a Mentor Commented:
select customers.customerid, name , email from customers inner join orders on orders.customerid = customers.customerid where orders.order_status = 'FULL'

you need to name the table.field if there are more options
0
 
ManishLeadCommented:
SELECT  customerid, name, email
FROM customers  JOIN orders ON customers. customerid = orders.customerid
WHERE order_status = 'FULL'  (if order_status is varchar type)
0
 
InsoftserviceCommented:
Hi,

SELECT  
           customerid, name, email
FROM
         customers  
JOIN
        orders ON
      customers. customerid = orders .customerid
WHERE
    order_status = 'FULL'

Hope it helps.

Is it that data in orders is not optional .
IF yes then use 'LEFT JOIN' instead of just 'JOIN'
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Neil_BradleyWeb UX/UI DeveloperAuthor Commented:
I am getting an error
"customerid in field list is ambigious"

Your answers should work but... I have ckecked again the names of the colums but cant spot the issue.
N
0
 
InsoftserviceCommented:
SELECT  
          orders.customerid, name, email
FROM
         customers  
JOIN
        orders ON
      customers. customerid = orders .customerid
WHERE
    order_status = 'FULL'

give columnname.name ,columnname.email
0
 
InsoftserviceConnect With a Mentor Commented:
SELECT  
          orders.customerid, customers.name, customers.email
FROM
         customers  
JOIN
        orders ON
      customers. customerid = orders .customerid
WHERE
    order_status = 'FULL'
0
 
Neil_BradleyWeb UX/UI DeveloperAuthor Commented:
Thanks for the solutions. p_nuts you got there first however insoftservice got the answer corect too. Thanks for your support. I can clock off work now!
N
0
 
ManishLeadCommented:
:(
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.