Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Inner Join Confusion

Author  Topic 

biggunn
Starting Member

4 Posts

Posted - 2010-12-20 : 15:19:00
I am having trouble with a query that I just can't figure out how to fix. Been working on this problem for two days and I need someone brilliant to help me fix it.

I have 4 tables:(only pertinent fields listed)
tblContacts: ContactID
tblLogins: CustID, ContactID
tblSalesPerson_Customer: spCustID, custCustID
tblCrushOrder: customerID

relationships are as follows:
tblCrushOrder.customerID = tblSalesPerson_Customer.custCustID
tblSalesPerson_Customer.custCustID = tblLogins.CustID
tblLogins.ContactID = tblContacts.ContactID


Here is the current query:
SELECT
tblCrushOrder.ID,
TBLCONTACTS.FName,
TBLCONTACTS.LName,
TBLCONTACTS.COMPANY,
tblCrushOrder.orderDate,
tblCrushOrder.orderStatus,
tblCrushOrder.OrderInvoicing,
tblCrushOrder.SalesPersonNumber,
tblCrushOrder.CPPO
FROM
(tblCrushOrder INNER JOIN tblSalesPerson_Customer ON tblCrushOrder.customerID = tblSalesPerson_Customer.custCustID)
INNER JOIN (TBLCONTACTS INNER JOIN tblLogins ON TBLCONTACTS.ContactID = tblLogins.ContactID) ON tblSalesPerson_Customer.custCustID = tblLogins.CustID
WHERE
tblSalesPerson_Customer.spCustID=15627 AND fname LIKE '%horn%' OR LName LIKE '%horn%' ORDER BY tblCrushOrder.ID DESC


The problem I am having is it is not filtering for the salespersonID (tblSalesPerson_Customer.spCustID=15627). The result is the same no matter what number I put in there or if it is there or not. How can I get it to show only the records that include the salespersonID (tblSalesPerson_Customer.spCustID)?

Thank you in advance.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-20 : 15:49:17
Wrap parentheses around the bit logic in the WHERE clause
WHERE	tblSalesPerson_Customer.spCustID = 15627
AND (fname LIKE '%horn%' OR LName LIKE '%horn%')
Go to Top of Page

biggunn
Starting Member

4 Posts

Posted - 2010-12-20 : 16:07:27
Thanks for the reply. That looks like that did the trick. I knew all it would take is someone brilliant. Thank you so much.
Go to Top of Page
   

- Advertisement -