I have five tables that contain artificial data about bank details.
My intention is to select only transactions that took place in a different city than that of the customer they belong to.
In order to do this, I wrote this query:
SELECT c.Cityid,c.FirstName,c.LastName,ci.Name
FROM dbo.Customer c
INNER JOIN dbo.City ci ON ci.ID=c.Cityid
INNER JOIN dbo.Account acc ON acc.CustomerId=c.Id
INNER JOIN dbo.AccountDetails accde ON accde.AccountId=acc.Id
INNER JOIN dbo.Location lo ON lo.LocationTypeId=accde.LocationId
WHERE c.Cityid <>lo.CityId
But unfortunately I have results that are not good and my code listed data which is identical like city of customer, not city of transaction. Probably I have some mistake with WHERE expression.
So can anybody help me how to fix this problem ?
Also here in attachment there is diagram of tables in database.
Additionally here I put screen-shot from Customer table.In last column here there is CityId marked with yellow color.
Each of this customer have some transaction in City which is diffrent than their cities.For example for customer with Id 1 I need to have all other cities that this customer have transaction.In order to achive this I connect customer table with account details and account table.And the end I need to have city for each cusmtomer which is diffrent than their city. Also here I will put Location table
If you want to display city of transaction too, join the city table again:
SELECT
c.FirstName,
c.LastName,
ci.Name as customer_city,
tci.Name as transaction_city
FROM dbo.Customer c
JOIN dbo.City ci ON ci.ID=c.Cityid
JOIN dbo.Account acc ON acc.CustomerId=c.Id
JOIN dbo.AccountDetails accde ON accde.AccountId=acc.Id
JOIN dbo.Location lo ON lo.LocationTypeId=accde.LocationId
JOIN dbo.City tci ON tci.ID=lo.Cityid -- Added join to city from location
WHERE c.Cityid <>lo.CityId
I added another join to City from Location.
I also removed the CityId column, because generally surrogate id’s are meaningless.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments