0

I am trying to create an app for the salespeople who work for my company to keep track of their sales/commission

When a salesperson brings on an account, their name is noted on the account and the table might look like

Table B

Account Salesperson
Account A A
Account B B
Account C B
Account D A

Then the jobs list might look like

Table A

Account Job details Price
Account A xyz £x
Account B xyz £x
Account C xyz £x
Account D xyz £x
Account A xyz £x
Account C xyz £x

I am looking for a way to filter the results of table A based on the sales person noted next to the Account in Table B

I have looked at all of the JOIN options but am unsure I am using the correct one.

I have opted, currently, for

SELECT J.*, A.Company, A.SalesPerson FROM TableA A
INNER JOIN TableB B
ON A.Account= B.Account
Where A.Salesperson = "B"

This seems to work for the time being, however I am unsure wether this is the best Join to use and whether it will last the test of time

2
  • 1
    Not related to your question about joins, but you should use single-quotes for string literals in SQL. 'B', not "B". Nov 29, 2022 at 16:38
  • 1
    Please don't mix table names and values -- It makes it too hard to follow.
    – Rick James
    Nov 29, 2022 at 20:26

1 Answer 1

1

Inner join is the correct type for what you described. Only rows that match the join expression will be included in the result.

You probably need an index to help optimize the join if the tables are expected to grow. I don't know what indexes you currently have in these tables, so I won't guess which index you need.

7
  • Thanks Bill. Can I ask what sort of index you mean? Like a separate table that links the 2 together?
    – PaulMcF87
    Nov 29, 2022 at 16:45
  • Indexes are not tables. They are like a sorted list of values in one column or a set of columns of one of your tables, to help improve lookup performance. You might like to read more about them here: use-the-index-luke.com or my presentation How to Design Indexes, Really or the chapter on indexing in my book SQL Antipatterns Volume 1 Nov 29, 2022 at 16:51
  • Thanks for that, Ill give it a look, although it looks a little over my head. My SQL knowledge is quite basic, I know what I need to know to get me by. MariaDB has an indexes tab... Is it possible to use this to create the indexes?
    – PaulMcF87
    Nov 29, 2022 at 17:02
  • I don't use MariaDB. Nov 29, 2022 at 17:07
  • Sorry Bill, It is MariaDB I am using but I meant to say HeidiSQL has an indexes tab... Is it possible to use this to create the indexes?
    – PaulMcF87
    Nov 29, 2022 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.