|
hbadministrator
Posting Yak Master
120 Posts |
 Posted - 2014-03-24 : 11:01:32
|
| Hello I have a very simple join/left join or right join. To be honest not sure what join I should be using. I have 4 tables. One is fuel, 2 of them are customer (same table) and the 4th is equipment. I am able to join the fuel and the customer tables no problem. Once I add the equipment table it gets all messed up by adding records. All I want is if the fuel table has any equipment post the extra_field1 - extra_field13 at the end of the file. If not don't post it.SELECT DISTINCT CONVERT(VARCHAR(10), FO_FuelRouting.DelDate, 101) AS DelDate, FO_FuelRouting.[Charge-cust], customer_1.name AS [Charge Name], FO_FuelRouting.FuelType, customer_1.[Tax-code], customer_1.[Type-code], customer_1.[Misc-Code], customer.name AS [Site Name], customer.Address, customer.City, customer.St, customer.[Zip-code], FO_FuelRouting.Critical, FO_FuelRouting.DelDriver, FO_FuelRouting.DelGallons, FO_FuelRouting.DeliveryStatus, FO_FuelRouting.DelLeftinTank, FO_FuelRouting.DelNotFilled, FO_FuelRouting.DelPrice, FO_FuelRouting.DelPricePerGallon, FO_FuelRouting.DelRunOut, FO_FuelRouting.Status, FO_FuelRouting.[Unit-No], FO_FuelRouting.[wo-no], FO_FuelRouting.DelTruck, equipment.[extra-field1], equipment.[extra-field2], equipment.[extra-field3], equipment.[extra-field4], equipment.[extra-field5], equipment.[extra-field6], equipment.[extra-field7], equipment.[extra-field8], equipment.[extra-field9], equipment.[extra-field10], equipment.[extra-field11], equipment.[extra-field12], equipment.[extra-field13]FROM FO_FuelRouting INNER JOIN customer AS customer_1 ON FO_FuelRouting.[Charge-cust] = customer_1.[Cust-no] INNER JOIN customer ON FO_FuelRouting.CustNo = customer.[Cust-no] LEFT OUTER JOIN equipment ON FO_FuelRouting.CustNo = equipment.[Cust-no] AND FO_FuelRouting.[Charge-cust] = equipment.[Charge-cust] |
|
|
sz1
Aged Yak Warrior
555 Posts |
Posted - 2014-03-24 : 11:19:47
|
| Left Join will give you everything on the left table even is there is no match on adjoining table, Right join will give you everything in the right table even is there is no match on the adjoining table, full join or inner join will return everything from both tables regardless of join type, you will also get NULL values...have you tried inner join as oppose to a left join for your equipment table? also are you sure your join fields are correct from you equipment join? why 2 tables with same cust info...If you will it you can achieve it!! |
 |
|
Simple join or outer join