StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL - create a view of multiple tables joined together
text
Body
copied!<p>I need to create a view of multiple tables joined to a table called <code>artists</code> so that it can be easily referenced. </p> <p>The arrangement of the data in the view is what i am struggling with. I need the joins to behave independently of each other so no data is duplicated. I'll give the query I have so far and then some examples;</p> <pre><code>CREATE VIEW stream_view AS SELECT A.ID, T.T_ID, T.name AS name, T.pic AS T_pic, T.timestamp AS T_ts, (SELECT COUNT(*) FROM track_plays WHERE T_ID = T.T_ID) AS plays, (SELECT COUNT(*) FROM track_downloads WHERE T.T_ID) AS downloads, (SELECT COUNT(*) FROM likes WHERE E_ID = T.ID AND event = 'T') AS T_likes, S.S_ID, S.status, S.timestamp AS S_ts, (SELECT COUNT(*) FROM likes WHERE E_ID = S.ID AND event = 'S') AS S_likes, G.G_ID, G.gig_name, G.date_time, G.lineup, G.price, G.currency, G.pic AS G_pic, G.ticket, G.venue, G.timestamp AS G_ts, (SELECT COUNT(*) FROM likes WHERE E_ID = G.ID AND event = 'G') AS G_likes FROM artists A LEFT JOIN status S ON S.ID = A.ID LEFT JOIN gigs G ON G.ID = A.ID LEFT JOIN tracks T ON T.ID = A.ID </code></pre> <p>I will make some examples but only include the ID's of everything for simplicity.</p> <p><strong>Example of what should happen</strong></p> <p>Say I have two 3 <code>A.ID</code>'s of 1, 2 and 3. Each of these ID's has a few <code>G_ID</code>'s, <code>T_ID</code>'s and <code>S_ID</code>'s belonging to them. The view should come out so there are no duplicates and each <code>G_ID</code>, <code>T_ID</code> or <code>S_ID</code> is on a different row and is assigned to their <code>A_ID</code>. Something like this;</p> <pre><code> A_ID G_ID T_ID S_ID 1 1 NULL NULL 1 NULL 4 NULL 2 NULL NULL 5 3 2 NULL NULL 3 NULL 8 NULL 3 NULL NULL 8 </code></pre> <p><strong>Example of what happens at the moment</strong></p> <pre><code> A_ID G_ID T_ID S_ID 1 1 NULL NULL 1 1 4 NULL 2 NULL NULL 5 3 2 NULL NULL 3 2 8 NULL 3 2 8 8 </code></pre> <p>As you can see the data is duplicated so that the table contains more than one of the same <code>G_ID</code>, <code>T_ID</code> or <code>S_ID</code>. These ID's are unique in their own column so their can't be duplicates of a <code>G_ID</code> but a <code>G_ID</code> and a <code>T_ID</code>, for example, can have the same values.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload