Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Leeds, UK
    Posts
    8

    Unanswered: outer join using SQL

    Hi there,

    I have 2 tables:

    LIST and LIST_CONTENT

    A simplistic view of them is as follows
    The LIST table has the field ID, LISTNAME
    The LIST_CONTENT table has the fields ID,LISTID,ITEMVALUE

    What i'm wanting to do is a single SQL query that can get the following data

    LIST.ID,LIST.LISTNAME,count(LIST_CONTENT.*) where LIST.ID=LIST_CONTENT.LISTID


    What i'm wanting in the query is to include a record in the results with a count of 0 if there are no records in the LIST_CONTENT table for the associated LIST.ID

    I know it's an outer join i need but i can for the life of me get it to work.

    I'm trying to develop a VC++ application that queries an access database.

    I'm sure this is simple but it wont work.

    Thanks in advance.

    Jateen

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select LIST.ID
         , LIST.LISTNAME
         , count(LIST_CONTENT.LISTID) as LISTIDs
      from LIST 
    left outer
      join LIST_CONTENT
        on LIST.ID
         = LIST_CONTENT.LISTID
    group
        by LIST.ID
         , LIST.LISTNAME
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    Leeds, UK
    Posts
    8
    Hi there,

    thanks for your reply which works great. I have another question regarding your post. Is it possible to extend the query so that the count only contains the number of records where, say, LIST_CONTENT.ITEMVALUE=Yes

    If i add the above into a where clause, it simply returns no records if there are no item in the LIST_CONTENT table instead of a record with count 0

    Hope you can help with this.

    Thanks
    Jateen

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select l.id, l.listname, count(lc.listid) as count
    from list l
    LEFT OUTER JOIN
    (select lc.*
    from list_content lc
    where lc.itemvalue = 'YES') V
    ON V.id = l.id
    GROUP BY l.id, l.listname;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Feb 2004
    Location
    Leeds, UK
    Posts
    8
    Thanks for all your help in this matter. With your assistance, the matter has now been resolved.

    Kind regards
    Jateen

Posting Permissions

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