return values of table 1 based on single column in table 2

Question

I have 3 tables that I am using and need to make a query to return data from one table based on the value of a single column in the second table.

tbl_user
- ID
- login
- pass
- active
- mscID
tbl_master
- ID
- name
- training_date
- MSCUnit
- Active
tbl_msc
- mscID
- mscName

my current SQL statement:

SELECT
   tbl_master.ID,
   tbl_master.name, 
   tbl_master.training_date, 
   tbl_master.MSCUnit,
   tbl_master.active, 
   tbl_user.mscID
 FROM
   tbl_master, 
   tbl_user
 WHERE
   tbl_master.active = 1 AND tbl_master.MSCUnit = tbl_user.mscID

The values stored in tbl_msc.mscID is a varchar(11) and it contains a string similar to A00 or A19. This is also the Primary key in the table.

The values stored in tbl_user.mscID matches that of tbl_msc.mscID. The values stored in tbl_master.UnitMSC also matches that of tbl_msc.mscID.

My goal is to return all records from tbl_master where the currently logged in user has the same mscID. The problem I am having is the statement returns all records in tbl_master.

I have tried several different join statements and for some reason, I cannot get this to filter correctly.

I am missing something. Any assistance in the SQL statement would be appreciated.

Thanks, Will

Your problem stems from using a Cartesian product via multiple tables in your `FROM` clause. Use an `INNER JOIN` instead — Phil, Aug 06 '18 at 00:01
Possible duplicate of [SQL Server query returning many cartesian product](https://stackoverflow.com/questions/42172576/sql-server-query-returning-many-cartesian-product) — Phil, Aug 06 '18 at 00:05
Also see [Performance of inner join compared to cross join](https://stackoverflow.com/questions/670980/performance-of-inner-join-compared-to-cross-join) — Phil, Aug 06 '18 at 00:06
And if you're still struggling, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Aug 06 '18 at 00:10

score 0 · Accepted Answer · answered Aug 06 '18 at 00:47

0

You should be writing this using joins. I don't know how you know who the current user is, but the idea is to join the three tables together:

SELECT m.ID, m.name, m.training_date, m.MSCUnit, m.active, 
       u.mscID
FROM tbl_master m JOIN
     tbl_user u
     ON m.MSCUnit = u.mscID JOIN
     tbl_msc msc
     ON msc.mscID = u.msc_ID
WHERE m.active = 1 AND msc.mscName = ?;

Notice the use of proper, explicit, standard JOIN syntax and table aliases.

answered Aug 06 '18 at 00:47

Gordon Linoff

1,242,037
58
646
786

1

that might be the step I'm missing. I assumed since the user logs into the system the table would know the active user. I guess I need to figure out a way to check for current user that is logged in. – Will D. Aug 06 '18 at 01:17

score -1 · Answer 2 · answered Aug 06 '18 at 00:26

-1

Select a.*, b.userid from 
table_master a, table_user b where 
a.mscunit in (select mscid from 
table_user where active=1)

This should point you in the right direction.

answered Aug 06 '18 at 00:26

Bleach

561
4
11

return values of table 1 based on single column in table 2

2 Answers2