I'm having a problem with record fields showing up when they shouldn't

[thefrstgatsby]

The first part of this message is a repost which was already answered, but after we decided the problem was solved, I realized there was one more thing that I need help on. So the question is at the bottom since the backstory to this is needed to understand.





Clients (table A) Service Prov (table b)
Name Transaction IDc Provider Transactionsp ID
------ --------------- -------- ----------------
Joe 434 Mary 434



Transactions table
Transaction IDt
---------------
434



Not doing business Anymore
--------------------------

Client Name Provider Name
----------- -------------
Joe Mary



So I need to pull all transactions from the Transactions table with the same transaction code, but only if there is a record in the Not Doing Business Anymore Which is related to the client and service provider tables via Client name and Provider Name. Plus I need to pull the record from the not doing business anymore as well.




Here is the solution, which does work in pulling the required records...






SELECT ...ListOfColumns...
FROM Transactions AS t
INNER JOIN Clients AS c
ON t.[Transaction IDt] = c.[Transaction IDc]
INNER JOIN [Service Prov] AS sp
ON t.[Transaction IDt] = sp.[Transactionsp ID]
INNER JOIN [Not doing business Anymore] AS ndba
ON c.[Name] = ndba.[Client Name]
AND sp.Provider = ndba.[Provider Name]





********However, here is the problem*********

And my question*****



when it returns the value for a row, if for example I have a column in the Not doing business Anymore table called "reason", it will copy that reason over any row associated with that record.

So let's say there was a transaction record for joe and mary under the transaction table, and then in the Not doing business Anymore table there is an associated table.

Instead of showing:

date client provider reason
02/16/2005 Joe Mary ------
03/17/2005 Joe Mary unhappy with service.


Which is what I want it to show, since the first row (02/16/2005) is only a transaction, and therefore the reason field should be blank, it shows the following:

date client provider reason
02/16/2005 Joe Mary unhappy with service.
03/17/2005 Joe Mary unhappy with service.

I tried a left join between the client / provider tables
and the "Not doing business anymore" table, but then it ends up returning more rows, which definitely should not exist.

What kind of join should I be making, or what should I change in order to make it work?

 

[Pattycake245]

I think, if it's possible you'll need a third field to join on. IE, if the Not Doing Business Anymore table has a date field in it, join on that as well, as joining by name, no matter what kind of join will add the reason caption to each instance of client and provider name.

SELECT ...ListOfColumns...
FROM Transactions AS t
INNER JOIN Clients AS c
ON t.[Transaction IDt] = c.[Transaction IDc]
INNER JOIN [Service Prov] AS sp
ON t.[Transaction IDt] = sp.[Transactionsp ID]
INNER JOIN [Not doing business Anymore] AS ndba
ON c.[Name] = ndba.[Client Name]
AND sp.Provider = ndba.[Provider Name]
AND sp.date = ndba.date

Tim

 

[thefrstgatsby]

I think that the problem will still exist, I'm getting the correct amount of records, but the reason column values are being placed into the the transaction record results as well. The reason column doesn't actually exist in the transaction record, so it should say 'NULL' in those cases.

 

[Pattycake245]

I think it would be best show to show all your tables with a test record or two and you whole query. There seems to be some holes in your request that are hard to decifer.

So I need to pull all transactions from the Transactions table with the same transaction code, but only if there is a record in the Not Doing Business Anymore Which is related to the client and service provider tables via Client name and Provider Name. Plus I need to pull the record from the not doing business anymore as well.

The transaction table obviously has some sort of date field in it, but how does the not doing business anymore table know which records to append the reason to? Or do you just want the reason filled in for one of the records no matter how many transaction there are.

Tim

 

[thefrstgatsby]

k, that part of the problem is already solved, the real issue is where I say

when it returns the value for a row, if for example I have a column in the Not doing business Anymore table called "reason", it will copy that reason over any row associated with that record.

So let's say there was a transaction record for joe and mary under the transaction table, and then in the Not doing business Anymore table there is an associated table.

Instead of showing:

date client provider reason
02/16/2005 Joe Mary ------
03/17/2005 Joe Mary unhappy with service.


Which is what I want it to show, since the first row (02/16/2005) is only a transaction, and therefore the reason field should be blank, it shows the following:

date client provider reason
02/16/2005 Joe Mary unhappy with service.
03/17/2005 Joe Mary unhappy with service.

 

[traingamer]

It's doing exactly what you are asking for. You are asking for 'reason' based on 'client' and 'provider'. The 'client' and 'provider' are the same 'Joe' and 'Mary' - therefore the reason associated with them in [Not doing business Anymore] is being displayed.

If you do not have a 'transaction' or some other foreign key (besides client and provider) associated with the [Not doing business Anymore] table, then you're pretty much stuck with the results you're getting.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[thefrstgatsby]

thanks, I figured as much, one more question though, let's say I want to join one table to another, but the second table is a compound key. in order to join it and avoid duplication, is this the proper syntax?

table a INNER JOIN
table b ON a.col1 = b.col1 and
a.col2 = b.col2

or does the inner join only count from the a.col1 = b.col1?

 

[traingamer]

You can link on both columns.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[thefrstgatsby]

so my syntax is correct then?

 

[Pattycake245]

Yes, your syntax is correct, as you'll notice in my first post I made the suggestion to add another join condition to solve you issues as was also suggested by traingamer.

Tim