Query to pull certain Customers and Required Doc Types 1

[LeonelSanchezJr]

I am trying to write a query to pull only certain records from Table_B where that customer code has the Required Document type.

Table_A contains the Customer Codes and Required Document Types.

Here's what I have so far:

Select Table_B.cDocNum, Table_B.cCustCode, Table_B.cDocType from Table_B
Left Join Table_C on Table_B.cDocNum = Table_C.cDocNum
Where Table_B.cCustCode In (Select Table_1.cCustCode
From Table_1)
And Table_B.dDueDate Between '06/25/01' and '06/28/01'
Order By Table_B.cCustCode, Table_B.cDocType,
Table_B.chawbnum

 

[tlbroadbent]

What's the question or problem? Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln

 

[LeonelSanchezJr]

Well, I have the cCustCode portion of it, however, I don't have the code to pull only the particular cDocTypes needed by each particular cCustCode.

Here is an example of what I mean:

cCustCode RequiredDocTypes
ABC123 DOCTYPE_1
ABC123 DOCTYPE_5
ABC123 DOCTYPE_8
DEF456 DOCTYPE_2
DEF456 DOCTYPE_4
GHI789 DOCTYPE_1
GHI789 DOCTYPE_2

 

[tlbroadbent]

I hope I understand correctly. Try this query.

Select
Table_B.cDocNum,
Table_B.cCustCode,
Table_B.cDocType

From (Table_B
Inner Join Table_A
On Table_B.cCustCode=Table_A.cCustCode
And Table_B.cDocType=Table_A.cReqDocType)
Left Join Table_C
On Table_B.cDocNum = Table_C.cDocNum
Where Table_B.dDueDate Between '06/25/01' and '06/28/01'

Order By
Table_B.cCustCode,
Table_B.cDocType,
Table_B.chawbnum Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln