php - MySQL: Can a join do the trick? -

i have tables:

------------------------ - user: ----- id     ----- name      ------------------------ - user_has_element: ----- user_id      ----- element_id      ------------------------ - element: ----- id ----- name ----- price ------------------------ 

my goal create function takes user name , element names, , returns following info every element passed it:

• name: element name
• price: element price
• isowned: whether or not element owned given user

so instance, let's user john has 3 elements e.a, e.b , e.c.

if call function following arguments:

    name: john     elements: e.a, e.b, e.c, e.d 

it should return array this:

        0:             - name: e.a                                         - isowned: 1         1:             - name: e.b                                         - isowned: 1         2:             - name: e.c                                         - isowned: 1         3:             - name: e.d                                         - isowned: 0                        

for now, i've got query returns elements owned user:

select e.name elementname user u inner join user_has_element h on h.user_id=u.id inner join element e on e.id=h.element_id     u.name='aaa'     ,     (         e.name='e.a'         or         e.name='e.b'         or         e.name='e.c'     )  

from there, know how work way goal of php code, wondered if possible mysql only?

yes, can select element table first , use left join there subquery check user_id of name passed. assuming function getting php variables like:

$user = 'john'; $elements = array('e.a', 'e.b', 'e.c', 'e.d'); $elements = implode("','",$elements); 

your sql this:

$sql = "     select          e.name elementname,         e.price elementprice,         (h.element_id not null) isowned     element e     left join user_has_element h on     (         h.element_id = e.id          , h.user_id =         (             select id              user u             u.name '" . $user . "'         )     )     e.name in ('" . $elements . "')     "; 

and of course, don't forget escape variables avoid sql injection.