Inner Join Confusion - SQL Server Forums

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%')

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources