views, joins and comparing a table against itself

[andyd273]

I have a view qrySearchFields with several tables in it, including
tblCustomer (CustomerID, CustomerName, CustomerPhone)
and
tblCustomerTypeLineItems (CustomerTypeLineItemID, CustomerID, CustomerTypeID)

so if I do a select on the view

SELECT * FROM qrySearchFields WHERE CustomerTypeID = '33'

returns

123,'Freds garage','5555554432',442,123,33
341,'Bills Automotive','5455553562',445,341,33

I'll get a list of customers with that type. great!
however, where I run into problems is that some customers have multiple types, so if I

SELECT * FROM qrySearchFields WHERE CustomerID = '123'
SELECT * FROM qrySearchFields WHERE CustomerID = '341'

I'll get

123,'Freds garage','5555554432',442,123,33
123,'Freds garage','5555554432',443,123,66
341,'Bills Automotive','5455553562',445,341,33
341,'Bills Automotive','5455553562',446,341,41

which is ok, unless I want to exclude by customer type

SELECT * FROM qrySearchFields WHERE CustomerTypeID = '33' AND NOT CustomerTypeID = '66'

still returns

123,'Freds garage','5555554432',442,123,33
341,'Bills Automotive','5455553562',445,341,33

while customer 123 should be excluded since its also type '66'.

SO...

What I think I need to do is to somehow join tblCustomerTypeLineItems to itself so that it'll check every line where customerID is 123 and exclude it if any of them are 66...

I just dont know how to do this, especially in a view.
Anyone? thanks in advance

 

[sodakotahusker]

use an alias to differentiate the same table in a query

select * from tblOne a
inner join tblOne b
on a.id = b.id

 

[SQLDenis]

SELECT * FROM qrySearchFields q WHERE CustomerTypeID = '33'
AND NOT EXISTS (select * from qrySearchFields q2
where g.CustomerID = q2.CustomerID
and CustomerTypeID <> '33' )

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[andyd273]

OK, so I get the alias thing, but will that work in a view?
I'm using the view to look up stuff in a VB6 program, so I just have to figure out how to rewrite it so that the program can write the sql statement for me to get what I want.

The guy that origionally wrote it had customer type in a comma delimited column under tblCustomers,
and had the program writing the query like

SELECT DISTINCT CustomerID, CustomerName, City, State, Zip, CustomerTypes FROM qrySearchFields WHERE CustomerTypes LIKE '%,31,%' And NOT CustomerTypes LIKE '%,66,%'

which worked, but I hate having the commas in there.
I can change how the program builds the queries, but if there is a way to keep the same structure

WHERE CustomerTypes LIKE '31' And NOT CustomerTypes LIKE '66' And NOT CustomerTypes LIKE '42' And NOT CustomerTypes LIKE '33'...

with out having to use aliases in the search query (which would make a complicated procdedure a lot more complicated, esp with multiple NOTs) that would be awesome.

 

[andyd273]

incase this helps at all, (if not, ignore)
here is the view structure:

ALTER VIEW dbo.qrySearchFields
AS
SELECT dbo.tblCustomers.CustomerName, dbo.tblCustomerPhone.PhoneNum AS PhoneNumber,
dbo.tblCustomerTypeLineItems.CustomerTypeID AS CustomerTypes, dbo.tblCustomerSubscriptions.SubscriptionID AS Subscription,
dbo.tblCustomerSubscriptions.SubscriptionDate, dbo.tblCustomers.CustomerID, dbo.tblCustomers.PAWKey, dbo.tblContactTypes.ContactType,
dbo.tblCustomerContactLogs.PromotionNumber, dbo.tblCustomers.EMailAddress, dbo.tblCustomers.AddDate,
dbo.tblCustomerSubscriptions.LabelName, dbo.tblCustomers.FaxNumber, dbo.tblCustomerLocations.LocationTypeID AS MailType,
dbo.tblCustomerLocations.Address1 AS Address, dbo.tblCustomerLocations.City, dbo.tblCustomerLocations.State, dbo.tblCustomerLocations.Zip,
dbo.tblCustomerLocations.Country, dbo.tblCustomerContacts.Title, dbo.tblCustomerContacts.FirstName, dbo.tblCustomerContacts.LastName,
dbo.tblCustomerContacts.Department, dbo.tblCustomerContactLogs.NextScheduledContact, dbo.tblCustomers.InvoicePCodes,
dbo.tblCustomers.CustStartDate, dbo.tblCustomers.ContactEmails, dbo.tblCustomers.DealerCode, dbo.tblCustomers.BACCode,
dbo.tblCustomers.[Zone], dbo.tblInvoices.InvoiceDate, dbo.tblCustomerContacts.ContactTypeID, dbo.tblCustomerComments.Comment,
dbo.tblCustomerComments.CommentTypeID AS CommentType, dbo.tblCustomers.DealerSize, RTRIM(dbo.tblEmployees.FirstName)
+ ' ' + RTRIM(dbo.tblEmployees.LastName) AS Employee, dbo.tblCustomerContacts.EMailAddress AS [Email Address for Text Export],
dbo.tblTestamony.TypeID AS TestamonyType
FROM dbo.tblCustomerLocations RIGHT OUTER JOIN
dbo.tblCustomerComments LEFT OUTER JOIN
dbo.tblTestamony ON dbo.tblCustomerComments.CustomerCommentID = dbo.tblTestamony.CustomerCommentID RIGHT OUTER JOIN
dbo.tblCustomers ON dbo.tblCustomerComments.CustomerID = dbo.tblCustomers.CustomerID LEFT OUTER JOIN
dbo.tblCustomerPhone ON dbo.tblCustomers.CustomerID = dbo.tblCustomerPhone.CustomerID LEFT OUTER JOIN
dbo.tblInvoices ON dbo.tblCustomers.CustomerID = dbo.tblInvoices.CustomerID LEFT OUTER JOIN
dbo.tblCustomerSubscriptions ON dbo.tblCustomers.CustomerID = dbo.tblCustomerSubscriptions.CustomerID LEFT OUTER JOIN
dbo.tblCustomerContacts LEFT OUTER JOIN
dbo.tblContactTypes ON dbo.tblCustomerContacts.ContactTypeID = dbo.tblContactTypes.ContactTypeID ON
dbo.tblCustomers.CustomerID = dbo.tblCustomerContacts.CustomerID ON
dbo.tblCustomerLocations.CustomerID = dbo.tblCustomers.CustomerID LEFT OUTER JOIN
dbo.tblTestamonyTypes ON dbo.tblTestamony.TypeID = dbo.tblTestamonyTypes.TypeID LEFT OUTER JOIN
dbo.tblEmployees RIGHT OUTER JOIN
dbo.tblCustomerContactLogs ON dbo.tblEmployees.EmployeeID = dbo.tblCustomerContactLogs.EmployeeID ON
dbo.tblCustomers.CustomerID = dbo.tblCustomerContactLogs.CustomerID FULL OUTER JOIN
dbo.tblCustomerTypeLineItems ON dbo.tblCustomers.CustomerID = dbo.tblCustomerTypeLineItems.CustomerID FULL OUTER JOIN
dbo.tblCustomerTypes ON dbo.tblCustomerTypeLineItems.CustomerTypeID = dbo.tblCustomerTypes.CustomerTypeID
GO