Welcome Guest, Not a member yet? Register   Sign In
How to do a join

I have 2 tables, table 1 is tbl_runs and table 2 is tbl_lots. for each lot record there are many run records which are related by a lotID field in the lots table and a lotID_FK field in the runs table.

I have a view that displays a table of all the run data but I want to include a field from the Lots table called LotSKU, which is in tbl_lots for each run record.

I know i need to use a join, but I'm not too familiar with joins yet. Can someone give me some help/pointers? Thanks


users_id (FK to users.id)

PHP Code:
->select(users.id as user_idusers.name as user_name//get what we want from users table
->select(permissions.name as permission_name//get what we want from permissions table
->join('permissions''permissions.users.id = users.id'//join the 2 tables where the users.id = the permissions.user_id

Lets says users looks like:
id: 1, name: john

Lets say permissions looks like:
id: 1, users_id: 1, name: Admin
id: 2, users_id: 1, name: Moderator

This shows we have 1 user with username john.
John has 2 permissions, Admin and Moderator (he has users_id entered 2x in permissions table)

So the returned data from the above query would produce:
user_id: 1, user_name: john, permission_name: Admin
user_id: 1, user_name: john, permission_name: Moderator

great! thanks, I think that answers my question, I'll post an update if I have anymore trouble though

Theme © iAndrew 2016 - Forum software by © MyBB