Display correct results in many to many relatonship tables

Question

I currently have three tables.

1. master_tradesmen

2. trades

3. master_tradesmen_trades (joins the previous two together in a many-to-many relationship). The 'trade_id' and 'master_tradesman_id' are the foreign keys.

Here is what I need to happen. A user performs a search and types in a trade. I need a query that displays all of the information from the master_tradesmen table whose trade in the master_tradesmen_trade table matches the search. For example, if 'plumbing' is typed in the search bar (trade_id 1), all of the columns for Steve Albertsen (master_tradesman_id 6) and Brian Terry (master_tradesman_id 8) would be displayed from the master_tradesmen table. As a beginner to SQL, trying to grasp the logic of this is about to make my head explode. I'm hoping that someone with more advanced SQL knowledge can wrap their head around this much easier than I can. Note: the 'trades' column in master_tradesmen is for display purposes only, not for querying. Thank you so much in advance!

Answer 1

You have a catalog for the tradesmen, & another catalog for the trades. The trades should only appear once in the trades catalog in order to make your DB more consistent

Then you have your many-to-many table which connects the trades & master tradesmen tables.

If we want to get the tradesmen according to the given trade in the input, we should first know the id of that trade which has to be unique, so in your DB you would have something like the img. below :

Now we can make a query to select the id of trade :

DECLARE @id_trade int = SELECT trade_id FROM trades WHERE trade_name LIKE '%plumbing%'

Once we know the trading id, we can redirect to the 'master_tradesmen_trades' table to know the name of the people how work that trade :

SELECT * FROM  master_tradesmen_trades WHERE trade_id = @id_trade

You will get the following result :

You may say, 'But there is still something wrong with it, as i am still not able to see the tradesmen', this is the moment when we make an inner join:

SELECT * FROM  master_tradesmen_trades trades_and_tradesmen
INNER JOIN master_tradesman tradesmen
ON tradesmen.id = trades_and_tradesmen.master_tradesmen_id
WHERE trade_id = @id_trade

IF you need to see specific columns, you can do :

SELECT first_name, last_name, city, state FROM  master_tradesmen_trades trades_and_tradesmen
INNER JOIN master_tradesman tradesmen
ON tradesmen.id = trades_and_tradesmen.master_tradesmen_id
WHERE trade_id = @id_trade