CODEWITHSUNDEEP
mysqlsql
ryanp
ryanp

Reputation: 23

How can I SQL Join against my user table in multiple columns?

I have a query to populate a DataTable. The database table has multiple columns which list an ID from my user table. They are case_officer, created_by, modified_by and location_user_id. Whenever I deal with one of those columns, I want to return the Rank and Last Name of the user from the user_account table instead of the user_id. I'm getting lost in the Concat and Join language. Here is what I have so far:

SELECT SQL_CALC_FOUND_ROWS item.item_id
     , item.item_code
     , item.check_out_flag
     , item.created_by
     , item.creation_date
     , CONCAT(user_account.rank, " ", user_account.last_name) AS case_officer
     , lookup_casetype.short_description AS case_type
     , item.incident_number
     , lookup_itemstatus.short_description AS item_status
     , lookup_finaldisposition.short_description AS final_disposition
     , lookup_danger.short_description AS danger_flag
     , lookup_mso.short_description AS most_serious_offense
     , item.item_description
     , lookup_itemtype.short_description AS item_type
     , lookup_drugpackaging.short_description AS drugs_packaging_type
     , item.drugs_substance, item.drugs_weight
     , item.make, item.model, item.caliber, item.serial, item.money_value
     , item.year, item.registration
     , lookup_state.short_description AS registration_state
     , lookup_color.short_description AS color, item.name_1
     , item.name_2, item.name_3, item.name_4
     , lookup_nametype.short_description AS name_1_type
     , lookup_nametype.short_description AS name_2_type
     , lookup_nametype.short_description AS name_3_type
     , lookup_nametype.short_description AS name_4_type
     , item.note, item.recorder_data, item.recovery_info
     , location.short_description AS location_id
     , lookup_itemclass.short_description AS item_class_id
     , CONCAT(user_account.rank, " ", user_account.last_name) AS modified_by
     , item.modified_date, location_user_id, item.deleted_flag, item.recovery_datetime
FROM item
LEFT JOIN lookup_itemstatus ON lookup_itemstatus.item_status_id = item.item_status
LEFT JOIN lookup_casetype ON lookup_casetype.item_type_id = item.case_type
LEFT JOIN lookup_finaldisposition ON lookup_finaldisposition.finaldisposition_id = item.final_disposition
LEFT JOIN lookup_danger ON lookup_danger.danger_id = item.danger_flag
LEFT JOIN lookup_mso ON lookup_mso.mso_id = item.most_serious_offense
LEFT JOIN lookup_itemtype ON lookup_itemtype.item_type_id = item.item_type
LEFT JOIN lookup_drugpackaging ON lookup_drugpackaging.drug_packaging_id = item.drugs_packaging_type
LEFT JOIN lookup_state ON lookup_state.state_id = item.registration_state
LEFT JOIN lookup_color ON lookup_color.color_id = item.color
LEFT JOIN lookup_nametype ON lookup_nametype.name_type_id = item.name_1_type AND item.name_2_type AND item.name_3_type AND item.name_4_type
LEFT JOIN location ON location.location_id = item.location_id
LEFT JOIN lookup_itemclass ON lookup_itemclass.item_class_id = item.item_class_id
LEFT JOIN user_account ON (user_account.user_account_id = item.case_officer)

ORDER BY item.item_code DESC
LIMIT 0, 150

... which works fine for case_officer, but I'm not sure how to do the same thing for modified_by, created_by and location_user_id without error.

Any help is appreciated!

Upvotes: 2

Views: 135

Answers (2)

Stan
Stan

Reputation: 983

Essentially a duplicate of How do I join same table multiple times?

You repeat the table name in the join list and use an ALIAS after the table name and before the ON join predicates. You also use the ALIAS in the field list expressions:

SELECT SQL_CALC_FOUND_ROWS item.item_id, item.item_code, item.check_out_flag, item.created_by, item.creation_date
, CONCAT(user_account.rank, " ", user_account.last_name) AS case_officer
-- here are the other user references:
, CONCAT(modified_by_user.rank, " ", modified_by_user.last_name) AS ModifiedByWho
, CONCAT(created_by_user.rank, " ", created_by_user.last_name) AS created_by_who
, CONCAT(location_user.rank, " ", location_user.last_name) AS location_Who
, lookup_casetype.short_description AS case_type, item.incident_number, lookup_itemstatus.short_description AS item_status, lookup_finaldisposition.short_description AS final_disposition, lookup_danger.short_description AS danger_flag, lookup_mso.short_description AS most_serious_offense, item.item_description, lookup_itemtype.short_description AS item_type, lookup_drugpackaging.short_description AS drugs_packaging_type, item.drugs_substance, item.drugs_weight, item.make, item.model, item.caliber, item.serial, item.money_value, item.year, item.registration, lookup_state.short_description AS registration_state, lookup_color.short_description AS color, item.name_1, item.name_2, item.name_3, item.name_4, lookup_nametype.short_description AS name_1_type, lookup_nametype.short_description AS name_2_type, lookup_nametype.short_description AS name_3_type, lookup_nametype.short_description AS name_4_type, item.note, item.recorder_data, item.recovery_info, location.short_description AS location_id, lookup_itemclass.short_description AS item_class_id, CONCAT(user_account.rank, " ", user_account.last_name) AS modified_by, item.modified_date, location_user_id, item.deleted_flag, item.recovery_datetime
                 FROM item
                        LEFT JOIN lookup_itemstatus ON lookup_itemstatus.item_status_id = item.item_status
                        LEFT JOIN lookup_casetype ON lookup_casetype.item_type_id = item.case_type
                        LEFT JOIN lookup_finaldisposition ON lookup_finaldisposition.finaldisposition_id = item.final_disposition
                        LEFT JOIN lookup_danger ON lookup_danger.danger_id = item.danger_flag
                        LEFT JOIN lookup_mso ON lookup_mso.mso_id = item.most_serious_offense
                        LEFT JOIN lookup_itemtype ON lookup_itemtype.item_type_id = item.item_type
                        LEFT JOIN lookup_drugpackaging ON lookup_drugpackaging.drug_packaging_id = item.drugs_packaging_type
                        LEFT JOIN lookup_state ON lookup_state.state_id = item.registration_state
                        LEFT JOIN lookup_color ON lookup_color.color_id = item.color
                        LEFT JOIN lookup_nametype ON lookup_nametype.name_type_id = item.name_1_type AND item.name_2_type AND item.name_3_type AND item.name_4_type
                        LEFT JOIN location ON location.location_id = item.location_id
                        LEFT JOIN lookup_itemclass ON lookup_itemclass.item_class_id = item.item_class_id
                        LEFT JOIN user_account ON (user_account.user_account_id = item.case_officer)
                        -- join to other users with ALIAS
                         LEFT JOIN user_account AS modified_by_user ON (modified_by_user.user_account_id = item.modified_by)
                         LEFT JOIN user_account created_by_user ON (created_by_user.user_account_id = item.created_by)
                         LEFT JOIN user_account location_user ON (location_user.user_account_id = item.location_user_id)

                 ORDER BY item.item_code DESC
                 LIMIT 0, 150

Note that the AS keyword for a table alias is optional. The aliases I used here were obnoxiously long. Many folks would use short aliases like umod, ucreated, uloc, etc.

Upvotes: 1

spencer7593
spencer7593

Reputation: 108400

Looks like you need a second join to the user_account table. At least one of the references will need a table alias to disambiguate the references. I'd give an alias to both references... co for the case_officer reference and mb for modified_by reference. Follow the same pattern for other joins to the same table.

e.g.

SELECT ...
     , ...
     , CONCAT(co.rank, " ", co.last_name) AS case_officer
     , ...
     , CONCAT(mb.rank, " ", mb.last_name) AS modified_by
     , ...
     , CONCAT(cb.rank, " ", cb.last_name) AS created_by
     , ...
     , CONCAT(lu.rank, " ", lu.last_name) AS location_user_id
     , ...

FROM ...
LEFT JOIN user_account co ON (co.user_account_id = item.case_officer)
LEFT JOIN user_account mb ON (mb.user_account_id = item.modified_by)
LEFT JOIN user_account cb ON (cb.user_account_id = item.created_by)
LEFT JOIN user_account lu ON (lu.user_account_id = item.location_user_id)

Upvotes: 0

Related Questions