I am stuck with some SQL query.
I have four tables. Which are connected:
From left to right they are one to many.
user_account has a user_id field as FK.
account_profile_field has user_account_id and profile_entry_id.
Profile_entries has a text field that I need to show for each user (account).
I need to write a query that will show me, all accounts for every user, and its profile entries.
I am sorry if this is confusing, I tried to make it simple
This is what I have done so far. I can show all accounts for every user and this is the point I am stuck with. Last two commented out Joins are not working properly. I believe I am close somewhat, I just need a push :)
u.email AS Email,
u.id AS UserId,
ua.id AS UserAccountId,
ua.app_id AS Application
FROM users AS u
INNER JOIN user_accounts ua ON ua.user_id = u.id
-- INNER JOIN account_profile_entries ape ON ape.user_account_id = ua.id
-- INNER JOIN profile_entries as pe ON pe.id = ape.profile_entry_id
Description :- The MySQL LEFT JOIN joins two tables and fetches rows based on a condition, which is matching in both the tables and the unmatched rows will also be available from the table written before the JOIN clause.
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
SELECT u.*, u.id AS UserId, ua.id AS UserAccountId, ua.app_id AS Application,pe.* FROM `users` u LEFT JOIN user_accounts ua ON ua.user_id = u.id LEFT JOIN account_profile_entries ape ON ape.user_account_id = ua.id LEFT JOIN profile_entries as pe ON pe.id = ape.profile_entry_id LIMIT 10