2 table inner join syntax problem

[Annoscia85]

Hi all,

I'm trying to join two tables together that dont have the same column names, I can get this to work on my other tables, but for some reason this join isn't returning the data from
the tables.

These are my two tables:-

Material_Cost
ID(PK)
Machine_Type
Hourly_Rate

Machine_Charge_Out
ID (PK)
Part_No-length
Weight_Per1000
Finished_Weight_kg
Scrap_Rate_kg
Costing_Time
Percentage
Scrap_Value

This is my join below:-

SELECT Machine_Charge_Out.Machine_Type, Material_Cost.Finished_Weight_kg
FROM Material_Cost INNER JOIN Machine_Charge_Out ON Material_Cost.Part_No_length = Machine_Charge_Out.Hourly_Rate;

Many thanks in advance

 

[Guus2005]

Part_No-length <> Part_No_length

one of the two doesn't exist probably. Or is it a typo?

and...
Material_Cost.Part_No_length = Machine_Charge_Out.Hourly_Rate
doesn't exist.

Machine_Charge_Out.Part_No_length = Machine_Cost.Hourly_Rate
does.

Again, is it a typo?

 

[Annoscia85]

Hey,

Thanks for the reply, I'm so stupid, type in my first post

the tables are


Machine_Charge_Out
ID(PK)
Machine_Type
Hourly_Rate

Material_Cost
ID (PK)
Part_No-length
Weight_Per1000
Finished_Weight_kg
Scrap_Rate_kg
Costing_Time
Percentage
Scrap_Value


and this is my statement


SELECT Machine_Charge_Out.Machine_Type, Material_Cost.Finished_Weight_kg, Material_Cost.Part_No_length
FROM Material_Cost INNER JOIN Machine_Charge_Out ON Material_Cost.Part_No_length = Machine_Charge_Out.Hourly_Rate;


Many thanks,

 

[Annoscia85]

and yes it should be Part_No_length that was also a typo

 

[Guus2005]

it doesn't matter that the names are different as long as the types are the same. if they are not you'll get an error message.

if you don't get any records then the fields you have joined don't have a match in the other table or one (or both) of your tables are empty.

hth:d

 

[Annoscia85]

Hey,

Many thanks, i have now have this working, but onto another inner join problem,

I want to join all of my tables together, these are

Costing_Main
Costing_Sub
Machine_Charge_Out
Material_Cost

the top 3 tables join fine and display the data that i want to view
Costing_Main
Costing_Sub
Machine_Charge_Out

but when i add the final table (Material_Cost), no data shows in the table, this is my inner join statement

SELECT Costing_Main.Enquiry_No, Costing_Main.Part_No, Costing_Main.Neida_Part_No, Costing_Main.Description, Costing_Main.Current_Date AS [Date], Costing_Main.Revision, Costing_Sub.Tool_Cost1, Machine_Charge_Out.Hourly_Rate, Material_Cost.Weight_Per1000
FROM Material_Cost INNER JOIN ((Costing_Main INNER JOIN Costing_Sub ON Costing_Main.Enquiry_No = Costing_Sub.Enquiry_No) INNER JOIN Machine_Charge_Out ON Costing_Sub.Machine_Type = Machine_Charge_Out.Machine_Type) ON Material_Cost.Weight_Per1000 = Machine_Charge_Out.Hourly_Rate
WHERE (((Costing_Main.Current_Date) Between #8/1/2011# And #8/31/2011#));

 

[Annoscia85]

Hi,

Nevermind just fixed my own problem..


SELECT Machine_Charge_Out.Machine_Type, Material_Cost.Finished_Weight_kg, Material_Cost.Part_No_length, Costing_Sub.Qty_Price_Break, Costing_Main.Enquiry_No
FROM Costing_Main INNER JOIN (Costing_Sub INNER JOIN (Material_Cost INNER JOIN Machine_Charge_Out ON Material_Cost.Part_No_length = Machine_Charge_Out.Hourly_Rate) ON Costing_Sub.Machine_Type = Machine_Charge_Out.Machine_Type) ON Costing_Main.Enquiry_No = Costing_Sub.Enquiry_No
WHERE (((Costing_Main.Current_Date) Between #8/1/2011# And #8/31/2011#));

 

[Guus2005]

glad you got that sorted.

till next time!

 

[Annoscia85]

Hi Guus2005,

I DID have this working, no suddenly its stopped

As before the 3 tables join perfectly:
Costing_Main
Costing_Sub
Machine_Charge_Out

but when i try to add my table Material_Cost, it returns no data from any of the tables.

so i broke it down, so i could try to add the Machine_Charge_Out and Material_Cost together. This is the code i have used...both Material_Cost.Part_No_length and Machine_Charge_Out.Hourly_Rate have the same data type (number)

SELECT Machine_Charge_Out.Machine_Type, Material_Cost.Finished_Weight_kg, Material_Cost.Part_No_length
FROM Material_Cost INNER JOIN Machine_Charge_Out ON Material_Cost.Part_No_length = Machine_Charge_Out.Hourly_Rate;

 

[Guus2005]

are you sure an id matches in both tables?

Material_Cost.Part_No_length = Machine_Charge_Out.Hourly_Rate

only when the above criteria is met, records will show.

how can you link an Hourly_Rate to Part_No_Length?

just because they are both numbers doesn't mean they should be equal.
an integer, long, double are all numbers, but they are not the same.

HTH

 

[Annoscia85]

you know what...

i used this..

SELECT Machine_Charge_Out.Hourly_Rate, Material_Cost.Weight_Per1000
FROM Material_Cost INNER JOIN Machine_Charge_Out ON Material_Cost.Part_No_length = Machine_Charge_Out.Hourly_Rate;

and now it works??

is there something im doing wrong in the first post today?

the join is the same i just chose a different column from material cost

 

[Annoscia85]

correction, this only shows one row of data in stead of showing all

SELECT Machine_Charge_Out.Hourly_Rate, Material_Cost.Weight_Per1000
FROM Material_Cost INNER JOIN Machine_Charge_Out ON Material_Cost.Part_No_length = Machine_Charge_Out.Hourly_Rate;

 

[Guus2005]

can you post a sample database?

 

[Annoscia85]

Hi Sorry its taken a while to post again.

attached is my sample database..

this is my full join on all 4 tables, which works now, but i can guarantee at some point it will stop working again


SELECT Costing_Main.Part_No, Costing_Main.Neida_Part_No, Costing_Main.Enquiry_No, Costing_Main.Description, Costing_Sub.Machine_Type, Machine_Charge_Out.Hourly_Rate, Costing_Main.Current_Date, Costing_Main.Revision, Material_Cost.Part_No_length
FROM Material_Cost INNER JOIN ((Costing_Main INNER JOIN Costing_Sub ON Costing_Main.Enquiry_No = Costing_Sub.Enquiry_No) INNER JOIN Machine_Charge_Out ON Costing_Sub.Machine_Type = Machine_Charge_Out.Machine_Type) ON Material_Cost.Part_No_length = Machine_Charge_Out.Hourly_Rate
ORDER BY Costing_Main.Current_Date;