Table: category  
-- idcategory (PK)  
-- idmember (FK: member that owns category)  

Table: category_shared UNIQUE (idcategory,idmember)   
-- idcategory (FK:referencing table category)  
-- idmember (FK:member being shared with)  

Table: last_viewed UNIQUE (idcategory,idmember)   
-- idcategory (FK:referencing table category)  
-- idmember (FK)  

I would like just one query that pulls all groups shared or possessed for any member and also the before that category was seen. This is exactly what I attempted:

SELECT * FROM (category 
LEFT JOIN category_shared USING (idmember))
INNER JOIN last_viewed ON 
    category.idcategory=last_viewed.idcategory OR 
    category_shared.idcategory=last_viewed.idcategory
WHERE category.idmember = '$member_id' OR category_shared.idmember = '$member_id'

But it is coming back SIX of the rows for every unique category. This complete database structure is a PITA in the get-go so I am available to recommendations for a much better schema.

Yes, well, half your troubles are because of not getting a resolved Data Model, which will cause issues in SQL everywhere should you fix that, the SQL is going to be much simpler. Another half is the inability to use SQL effectively.

  1. You haven't published info re another tables, and so i can't provide you with a reasonably accurate DM, I'm going just from that which you have given, not to mention made huge presumptions concerning the various other tables, that are most most likely incorrect. Should you supply the info, I'm able to offer an accurate DM.

    • Is really a Category Independent or "possessed" by just one Member ?
      • Modelled: "No."
    • On which basis is really a Category "shared" ?
      • Modelled: discussing is equivalent to possession a shared Category is only one that's possessed by several Member
    • Then, what's the distinction between an "possessed" Category along with a "shared" Category ?
    • We don't understand what the Groups are for (Subjects ?). By which situation how come Groups, not Subjects, possessed/shared by People ?
    • Your Category, if it's possessed, must not be known as Category, rather something similar to CategoryOwned.
    • I am unable to observe how CategoryViewed shows all of the Sights it enables just one (the final ?) view per Member. Where's the ViewDate ?
      • Modelled: Like a log of Sights. If you would like just one (the final) view per Member, remove ViewDate in the PK.

    Interim Data Model

  2. It's not recommended to do Unions, etc, unless of course you will need to. At this time the information is small once the database is big, you'll certainly have the results of the job tables being produced filled destroyed. To date out of your requirement, there's no requirement for Unions, Distincts, worktables, etc. However the DM does present problems.

  3. SQL. I am unable to observe how your SQL (or the other solutions) retrieves your dependence on "the final time that category was seen [by any Member]". Furthermore, it is best to attempt to get the SQL correct (coming back the right group of data) for every set, before Unioning, etc: you haven't done that that's how you get duplicate rows. Using DISTINCT is really a stupid method to repair the problem you're correct in seeking understanding rather.

  4. I will not make an effort to supply the SQL for the model. This is actually the SQL for that Interim Data Model provided. Clearly, this really is much, less expensive than Unions:

    SELECT  [Member]   = Member.Name,
            [Category  = Category.Name,
            [LastView] = ( SELECT MAX(ViewDate)
                FROM  CategoryView
                WHERE CategoryId = cm.CategoryId
                )
    FROM  CategoryMember cm,
          Member         m,
          Category       c
    WHERE cm.MemberId  = '$MemberId'
    AND   m.MemberId   = cm.MemberId
    AND   c.CategoryId = cm.CategoryId

In case your only issue is multiple identical rows came back, then DISTINCT should get the job done.

In the event that provides you with performance problems (it should not when the amounts of duplicate rows are while you indicate), complain together with your DBMS vendor or obtain a better DBMS.

Join Category Straight to last_seen when you are only showing data from individuals tables,

Make use of a Where IN category_shared to limit the information