SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    select distinct when tables are joined.

    Hi all

    I am trying to select a distinct record from one table when it is joined to other tables in the database. I have 4 tables: calendar, type, category and items.

    So far the mysql statement is

    Code MySQL:
    SELECT *
    FROM aCalendar, aType, aCategory, aItems
    WHERE aType.aTypeId = aCalendar.aCalaTypeId AND aType.aTypeId = 2 AND aCalendar.aCalDate > NOW() AND aItems.aItemCatId =aCategory.aCatId
    ORDER BY aCalendar.aCalDate ASC

    I want to select distinct aCalDate from calendar.

    I think that what is required is to completely re-write the statement above, along these lines:

    Code MySQL:
    SELECT DISTINCT aCalDate FROM aCalendar LEFT JOIN aCalendar.acalType = aType.aTypeId AND aType.aTypeId = 2 AND aItems.aItemCatId =aCategory.aCatId WHERE aCalDate > NOW()

    As you can probably tell I just use Dreamweaver to create my mysql statements normally and dont have a good understanding of joins, but am I going in the right direction? I think I have normalised my tables properly.

    Can anyone help me to set this out properly?

    any help greatly appreciated.

    Kind regards

    Paul

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    I'm not sure what exactly you want the query to do, but you can try this:
    Code:
    SELECT DISTINCT aCalDate
    FROM aCalendar
    INNER JOIN aType
    WHERE aType.aTypeId = aCalendar.aCalaTypeId 
    INNER JOIN aCategory
    ... the link between aCategory and aCalDate or aType is missing!
    INNER JOIN aItems
    ON aItems.aItemCatId =aCategory.aCatId
    WHERE aType.aTypeId = 2 
    AND aCalendar.aCalDate > NOW() 
    ORDER BY aCalendar.aCalDate ASC
    As you can see, the link between aCategory and the other tables of the query is missing.

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you for looking guido2004

    I think in the haze of trying to work it out, I've gone overboard with my statement and can simplify it.

    You help with the join syntax ivery useful though.
    Kind regards
    Paul


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •