Help with joins I think

[Cpreston]

Hi

We have table sin our sql which are customer, orderheader, customeraddress and journey header

I am trying to get a view that shows me the

Name of the customer
Address lines for the delivery by a particular journey number.

I am getting the right number of results back from the query but incorrect address information. it appears to be taking the customer ordering post codes form the oderheader and not the customeraddress post code (customer address is basically a delivery address).

I ma for example getting the postcode OX17 3SN where on the order and journey header it is being delivered to OX16 4TB

I have tried different joins but still get the same postcodes. Could someone advise what I need to do please to get the right result. Thanks


SELECT dbo.Customer.CustomerCode, dbo.Customer.Name, dbo.CustomerAddress.Address1, dbo.CustomerAddress.Address2, dbo.CustomerAddress.Address3,
dbo.CustomerAddress.City, dbo.CustomerAddress.County, dbo.CustomerAddress.PostCode, dbo.JourneyHeader.JourneyNumber
FROM dbrderHeader INNER JOIN
dbo.CustomerAddress ON dbrderHeader.DeliveryAddressID = dbo.CustomerAddress.CustomerAddressID INNER JOIN
dbo.JourneyHeader ON dbrderHeader.JourneyID = dbo.JourneyHeader.JourneyID INNER JOIN
dbo.Customer ON dbrderHeader.CustomerID = dbo.Customer.CustomerID
WHERE (dbo.JourneyHeader.JourneyNumber = 66183)

 

[Cpreston]

Hi

Its ok found other useful tables to sue on the DB. Not an issue anymore.

 

[fredericofonseca]

hi,

Could you please follow the advise that was given several times and both format your sql, use table Alias to reference your table columns and when posting use the [ignore]

[/ignore] constructs.
This will help people here understand faster what your code is trying to do and the first 2 aspects of it will help you and whoever looks at your code within your company.

Regarding your problem and as stated before on one of your other posts you are the one that should know how your tables and data link. And as such identify why a bit of data returned by your sql's isn't what you think it should be.

SELECT c.CustomerCode
     , c.Name
     , ca.Address1
     , ca.Address2
     , ca.Address3
     , ca.City
     , ca.County
     , ca.PostCode
     , jh.JourneyNumber
FROM dbo.OrderHeader oh
INNER JOIN dbo.CustomerAddress ca
   ON oh.DeliveryAddressID = ca.CustomerAddressID 
INNER JOIN dbo.JourneyHeader jh
   ON oh.JourneyID = jh.JourneyID 
INNER JOIN dbo.Customer c
   ON oh.CustomerID = c.CustomerID
WHERE (oh.JourneyNumber = 66183)

from the code above we will need the following information
table structure for OrderHeader, CustomerAddress and JourneyHeader. Customer should not be required to help but supply it anyway.
Output of the following queries

select oh.*
FROM dbo.OrderHeader oh
WHERE oh.JourneyNumber = 66183

select ca.*
FROM dbo.OrderHeader oh
INNER JOIN dbo.CustomerAddress ca
   ON oh.DeliveryAddressID = ca.CustomerAddressID 
  and oh.JourneyNumber = 66183

select jh.*
FROM dbo.OrderHeader oh
INNER JOIN dbo.JourneyHeader jh
   ON oh.JourneyID = jh.JourneyID 
  and oh.JourneyNumber = 66183

be sure to replace any personal identifier information with different values but consistently. e.g. if OrderHeader contains name JOHN and CustomerAddress contains name JOHN change both to Charlie as an example.
If on any of the above tables you can also remove any credit card information as well as any product(goods) information and any monetary amount as not required to help you

put this information well formated here - do not link to a outside spreadsheet please and do not just paste an image either please.

Once you have this info also put the output of your own original query as well as the output you expect and which record from the sql's I gave above should that information come from.




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886