mildly complex select statement with compound key 1

[thefrstgatsby]

I have a sql database, and I'm trying to pull records from tables a main table related to to other tables, where records from those two tables must be in another table as well.


Here is a diagram



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.


How do I do this?

 

[JohnDTampaBay]

This should get you started in the right direction. Good luck!

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]

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[thefrstgatsby]

Great, I did this and wasn't sure if it was right, so this should return the not doing business records as well as the transaction records?

 

[thefrstgatsby]

In addition to my reply I have one more question, are JOIN and INNER JOIN the same thing?

 

[JohnDTampaBay]

Since I used all INNER JOINs the data must appear in all tables to be returned.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[thefrstgatsby]

One last thing, when it returns the value for 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?