• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

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
0
Neil_Bradley
Asked:
Neil_Bradley
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
p_nutsCommented:
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
 
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
 
InsoftserviceCommented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now