CODEWITHSUNDEEP
mysqlsqldatabaseselectjoin
user1345603
user1345603

Reputation: 5

SQL Select based on certain Data

I have a few tables in my database:

  1. user table: user_id (primary key), first_name, last_name, email_address.
  2. inst table: inst_id (primary key), inst_name, and type.
  3. user_insts table: user_insts_id (primary key), user_insts_status, inst_name (foreign key) and user_id(foreign key).

I'm using this on my website and i need it to display all of the entrys in the inst_name column for the inst table, but only the entrys for a certain id in the right side or else show as null. I've tried a few things like below:

SELECT inst.inst_name,inst.inst_id,user_insts.user_id,user_insts.inst_name 
FROM inst LEFT JOIN user_insts ON inst.inst_name=user_insts.inst_name;

SELECT inst.inst_name,inst.inst_id,user_insts.user_id,user_insts.inst_name 
FROM inst LEFT JOIN user_insts ON inst.inst_name=user_insts.inst_name 
WHERE user_insts.user_id='11';

Any help would be greatly appreciated.

EDIT::

this is what i currently get: 

 inst_name  inst_id user_id:
 ASB          1        11
 BNZ          3        11
 FMG          5        11

i was hoping to be able to get something more like this:

 inst_name  inst_id user_id:
 ASB           1       11
 ANZ           2      NULL
 BNZ           3       11
 paymark       4      NULL
 FMG           5       11
 STATE         6      NULL

Upvotes: 0

Views: 168

Answers (3)

Jensen Ching
Jensen Ching

Reputation: 3164

Do your provided queries show the following results?

  1. Query 1 : Shows all inst + user_insts entries but unfiltered by user_id
  2. Query 2 : Shows just the inst + user_insts entries filtered by user_id

What's happening in Query 2 is that the where clause filters the joined tables AFTER they are joined. I guess what you want to happen is to first filter the right side of the result (user_insts) by a specific user_id like so:

SELECT ui.inst_name, ui.user_id FROM user_insts ui WHERE ui.user_id = :PARAM

Then, you want to LEFT JOIN this with all the entries in the inst table AFTER the filter. You could use an inner view in order to filter by user_id first before the actual joining to the inst table. The resulting query should be something like this:

SELECT i.inst_name, filtered_ui.user_id
FROM  inst i
LEFT JOIN (SELECT ui.inst_name, ui.user_id
           FROM user_insts ui
           WHERE ui.user_id = :PARAM) filtered_ui
ON i.inst_name = filtered_ui.inst_name

Thinking about it more, I'm not too familiar with MySQL so I'm not sure if this alternative query is valid syntax:

SELECT i.inst_name, ui.user_id
FROM inst i
LEFT JOIN user_insts ui ON i.inst_name = ui.inst_name AND ui.user_id = :PARAM

... which may be simpler than an inner view.

The main point is you have to do the filtering first before joining, so that all the inst_names will be displayed.

Upvotes: 0

Chris Moutray
Chris Moutray

Reputation: 18349

You question isn't 100% clear so I'm making the assumption that you want to show the inst_name for each user; showing the inst_name as null when an inst entry doesn't exist.

Something like this should work:

select u.user_id, i.inst_name
from user u
left join user_insts ui on ui.user_id = u.user_id
left join inst i on i.inst_name = ui.inst_name

You can constrain the results by user_id by adding 

where u.user_id = '11'

Upvotes: 0

liquorvicar
liquorvicar

Reputation: 6096

What your original query will do is to get all rows from the inst table and then see whether there is a row that matches on inst_name in your user_insts table. If there is it will return the data from that table. Otherwise it will return NULLs. If you change the JOIN to be an INNER JOIN then it will only return rows where the right-hand side matches. Like this:

SELECT inst.inst_name,inst.inst_id,user_insts.user_id,user_insts.inst_name 
FROM inst INNER JOIN user_insts ON inst.inst_name=user_insts.inst_name 
WHERE user_insts.user_id='11';

But you should look at changing you schema. You have an integer primary key on inst so you should use that rather than inst_name as the foreign key on inst_name.

Upvotes: 1

Related Questions