Databases
StackOverflow2013
Postsdbo
POMySQL select distinct across multiple tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL select distinct across multiple tables
    primarykey
    data
    text
    <p>I have a query that selects all columns from multiple tables, but it's returning multiples of the same values (I only want distinct values).</p> <p>How can I incorporate something like this? When I try this, it still </p> <pre><code>Select Distinct A.*, B.*, C.*.... </code></pre> <p>Does distinct only work when selecting the column names and not all (*) ? In <a href="http://www.w3schools.com/sql/sql_distinct.asp" rel="nofollow">this</a> reference it says distinct in reference to column names, not across all of the tables. Is there any way that I can do this?</p> <p><strong>edit - I added more info below</strong></p> <p>Sorry guys, I just got back onto my computer. Also, I just realized that my query itself is the issue, and Distinct has nothing to do with it.</p> <p>So, the overall goal of my Query is to do the following</p> <ol> <li>Generate a list of friends that a user has</li> <li>Go through the friends and check their activities (posting, adding friends, etc..)</li> <li>Display a list of friends and their activities sorted by date (I guess like a facebook wall kind of deal).</li> </ol> <p>Here are my tables</p> <pre><code>update_id | update | userid | timestamp //updates table post_id | post | userid | timestamp //posts table user_1 | user_2 | status | timestamp //friends table </code></pre> <p>Here is my query</p> <pre><code>SELECT U.* , P.* ,F.* FROM posts AS P JOIN updates AS U ON P.userid = U.userid JOIN friends AS F ON P.userid = F.user_2 or F.user_1 WHERE P.userid IN ( select user_1 from friends where user_2 = '1' union select user_2 from friends where user_1 = '1' union select userid from org_members where org_id = '1' union select org_id from org_members where userid = '1' ) ORDER BY P.timestamp, U.timestamp, F.timestamp limit 30 </code></pre> <p>The issue I'm having with this (that I thought was related to distinct), is that if values are found to meet the requirements in, say table Friends, a value for the Posts table will appear too. This means when I'm displaying the output of the SQL statement, it appears as if the Posts value is shown multiple times, when the actual values I'm looking for are also displayed</p> <p>The output will appear something like this (notice difference between post value in the rows)</p> <pre><code>update_id | update | userid | timestamp | post_id | post | userid | timestamp | user_1 | user_2 | status | timestamp 1 | update1 | 1 | 02/01/2013 | 1 | post1| 1 | 2/02/2013| 1 | 2 | 1 | 01/30/2013 1 | update1 | 1 | 02/01/2013 | 2 | post2| 1 | 2/03/2013| 1 | 2 | 1 | 01/30/2013 </code></pre> <p>So, as you can see, I thought I was having a distinct issue (because update1 appeared both times), but the query actually just selects all the values regardless. I get the results I'm looking for in the Post table, but all the other values are returned. So, when I display the table in PHP/HTML, the Post value will display, but I also get duplicates of the updates (just for this example)</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser1852176
    1. USuser1852176
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL select distinct across multiple tables
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL select distinct across multiple tables
      1. This table or related slice is empty.
      1. VTDownMod
    1. COHow are these tables related? Sounds like maybe you ought be doing a `UNION`... Post their schemas.
      singulars
      1. POMySQL select distinct across multiple tables
      1. USMichael Berkowski
    2. COCan you provide an example of the data you are using and the results you want? `SELECT DISTINCT` works. Your expectation of what it does needs to be updated.
      singulars
      1. POMySQL select distinct across multiple tables
      1. USGordon Linoff
    3. CODon't you have some auto_increment there? The question is so vague ...
      singulars
      1. POMySQL select distinct across multiple tables
      1. USNarcis Radu
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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