mysqlsqlsocialenginemariasql

SELECT from different table and LEFT JOIN


The original query is

SELECT user_id, user_location, displayname FROM engine4_users WHERE user_location IS NOT NULL

Which prints the user_id, user_location and displayname,

Ex:
1 30,32 demo
2 30,31 demo
3 33,32 demo

but now i need ot get the user picture from another table and i have this,

SELECT  s.storage_path as url FROM engine4_storage_files as s
LEFT JOIN  engine4_users as u ON s.file_id = u.photo_id

Which prints the URL only

What I need is to add that url to the first query


Solution

  • Try this:

    SELECT u.user_id, u.user_location, u.displayname, s.storage_path as url 
    FROM engine4_users u 
    LEFT JOIN engine4_storage_files s
    ON s.file_id = u.photo_id 
    WHERE u.user_location IS NOT NULL

    Extended by request in comments:

    SELECT u.user_id, u.user_location, u.displayname, w.value as about, s.storage_path as url 
    FROM engine4_users u 
    LEFT JOIN engine4_user_fields_values w 
    ON w.item_id = u.user_id
    LEFT JOIN engine4_storage_files s
    ON s.file_id = u.photo_id 
    WHERE u.user_location IS NOT NULL