Joining 3 tables to get the data from the 3rd table? Help!?

[Individual]

Hello, so I'm working in phpMyAdmin and I'm having trouble trying to get the answer I need. Basically, this is what I have to work with

 

The below is the tables and the links between them

 

customer: Customername, CustomerNum

 

orders: CustomerNum, OrderNum

 

orderline: OrderNum, PartNum

 

Now, what I'm trying to do is make a query to list a person or persons who order a specific part number. I don't know if this is the right way to do but here is what I've done

 

Select customer.Customername

From customer, order, orderline

where customer.Customername = customer.CustomerNum

and

customer.CustomerNum = orders.CustomerNum

and

orders.CustomerNum = orders.OrderNum

and

orders.OrderNum = orderline.OrderNum

and

orderline.OrderNum = orderline.PartNum

and

orderline.PartNum = 'part number';

 

 

I get 0 results back eventhough there is a specific part number that I can type in that I know is in orderline. Am I doing this wrong? Do I need to be doing join functions instead? Please help. Thank for your time.

Edited February 24, 2014 by Individual

[justsomeguy]

You're already doing a full inner join between the three tables. If it doesn't return any results, then at least one of the conditions in the WHERE clause is failing. I would suspect this one:

customer.Customername = customer.CustomerNum

How often is the customer name the same as the customer number? Or this:

orders.CustomerNum = orders.OrderNum

Are the customer and order numbers the same? Same thing here:

orderline.OrderNum = orderline.PartNum

The order numbers are the same as the part numbers? With those 3 conditions, you are saying that you want records where the customer name, customer number, order number, and part number are all the same value.What you should do is start the query with no conditions, and see what the inner join returns. Then add the WHERE conditions one at a time. When you add a condition and then it doesn't return any results, then nothing matches that condition.

[Individual]

Thank you very much. This was what I came up with after taking your advice.

 

Select Customername

FROM customer, orders, orderline

where customer.CustomerNum = orders.CustomerNum and orders.OrderNum = orderline.OrderNum and orderline.PartNum = 'partnumber';

 

My mistake in thinking originally was thinking we have to relate each variable with another variable in a table instead of just using the linking variables.... If that makes sense. I also figured out how to do it using Inner Join. Thank you sooooooo much for taking time out to answer this question. Much appreciated. I didn't think it would be answered so quickly.