0

Could you please help me with this issue?

I have two tables - one lists salesman data (customer account, product line and salesman's name.)

The other table has the customer account, product line brought and various sales value fields such as cost, discount, net value etc.

We can have one customer who has two salesman selling them two different product lines.

I am now trying to create a view that returns salesman, customer account, product line, sales invoice total etc.

This is my query:

SELECT dbo.SMM_IPUSALESMAN.SALESMAN, dbo.IREPORTS_MASTER.CUSTACCOUNT,
       dbo.IREPORTS_MASTER.PRODUCTLINE, dbo.IREPORTS_MASTER.INVTOTAL
FROM dbo.IREPORTS_MASTER
INNER JOIN dbo.SMM_IPUSALESMAN
   ON  dbo.IREPORTS_MASTER.CUSTACCOUNT = dbo.SMM_IPUSALESMAN.CUSTACCOUNT 
   AND dbo.IREPORTS_MASTER.PRODUCTLINE = dbo.SMM_IPUSALESMAN.PRODUCTLINE`

Querying IREPORTS_MASTER returns 6000 rows, but the above query returns more hence me thinking something is wrong. I also will have a scenario where some accounts do not have a SALESMAN assigned (house accounts) so expect them to have no values but still show invoice total.

Could some explain what I am doing wrong with the query? Is the join wrong? Thanks for any help.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Sagar Hussain
  • 1
  • Probably a IREPORTS_MASTER can reference several rows in SMM_IPUSALESMAN, and/or the opposite. – jarlh Jul 05 '16 at 09:23
  • You need to get little more input on JOINS I think. Usually after joining 2 tables , you may get more/less/same rows based on the reference of rows in those tables. – an33sh Jul 05 '16 at 09:25
  • Please edit your question to include the relevant tables DDLs, some DML for sample data and desired results. – Zohar Peled Jul 05 '16 at 09:26
  • There's a book by Martin Gruber, the examples in it are based on almost completely the same schema that you have. I think he will explain you everything better thay anyone else :) https://www.amazon.com/Mastering-SQL-Martin-Gruber/dp/0782125387 – Dany Jul 05 '16 at 09:26
  • @Sagar, it seems to me that one customer can buy more than one item from particular productline. `GROUP BY` you need Luke. – Ivan Starostin Jul 05 '16 at 10:02
  • Thank you Ivan - I should use group - I also have brought the book by Martin Gruber and read the following article:- http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins?rq=1 which also has helped. - thank you all for helping – Sagar Hussain Jul 05 '16 at 11:22
  • Your query and described behaviour sound correct to me! Given the fact that you have customers with more than one salesman, this would explain why you receive more than 6000 rows. Additionally, if you want records from IREPORTS_MASTER, which do not match any records in the salesman table, simply use a LEFT OUTER JOIN. – Tyron78 Jul 05 '16 at 12:21

0 Answers0