Professional Web Applications Themes

JOIN? - MySQL

I have two tables - "mailinglist" and "mailinglist_addresses". MailingList - id (PK) - name MailingListEmails - mailinglist_id - emailaddress - PK is mailinglist_id AND emailaddress I want to SELECT a result of id, name, emailcount WHERE emailcount is the number of emailaddresses for that record's ID. Can anyone help me with a join or somesuch? I still don't do joins well....

  1. #1

    Default JOIN?

    I have two tables - "mailinglist" and "mailinglist_addresses".

    MailingList
    - id (PK)
    - name
    MailingListEmails
    - mailinglist_id
    - emailaddress
    - PK is mailinglist_id AND emailaddress

    I want to SELECT a result of
    id,
    name,
    emailcount

    WHERE emailcount is the number of emailaddresses for that
    record's ID.

    Can anyone help me with a join or somesuch? I still don't do
    joins well.

    Sanders Guest

  2. #2

    Default Re: JOIN?

    Sanders,

    Assuming your mailinglist_id column in the MailingListEmails table is a
    foreign key referenceing MailingList, this should work for you:

    select m.ID, m.Name, count(*) EmailCount
    from MailingList m
    inner join MailingListEmails e on m.ID = e.MailingList_ID
    where e.MailingList_ID is null
    group by m.ID, m.Name

    -- Bill

    "Sanders Kaufman" <net> wrote in message
    news:rBYNh.3283$news.prodigy.net... 


    AlterEgo Guest

  3. #3

    Default Re: JOIN?

    Logically, yeah it's a foreign key. But there was nothing about
    FK mentioned in the CREATE TABLE for it - that's okay, right?


    AlterEgo wrote: 
    >
    >[/ref]
    Sanders Guest

  4. #4

    Default Re: JOIN?

    Sanders Kaufman <net> wrote in
    news:lOZNh.2791$news.prodigy.net:
     

    Correct, you do not need to explicitly specify them in table definitions,
    but it might help you keep track across a large amount of tables...
    Good Guest

  5. #5

    Default Re: JOIN?

    Sanders,

    You're good to go, a foreign key doesn't have to be declared for joins to
    work. It serves to describe the relationships within the database and can
    also enforce referential integrity if so declared.

    -- Bill

    "Sanders Kaufman" <net> wrote in message
    news:lOZNh.2791$news.prodigy.net... 
    >>[/ref][/ref]

    AlterEgo Guest

  6. #6

    Default Re: JOIN?

    Thanks - that's what I needed to know.


    AlterEgo wrote: [/ref]
    >[/ref]
    MrK Guest

  7. #7

    Default Re: JOIN?

    On Mar 26, 5:26 pm, "AlterEgo" <com> wrote: 



    > [/ref]

    Is there a functional difference between left join and inner join?
    I'm trying to get something similar working, and I'm new to sql.

    Chad

    chadlupkes Guest

  8. #8

    Default Re: JOIN?

    chadlupkes wrote: [/ref]
    >
    > Is there a functional difference between left join and inner join?
    > I'm trying to get something similar working, and I'm new to sql.
    >
    > Chad
    >[/ref]

    Chad,

    Yes. INNER JOIN returns rows only when there is a match in the JOIN.
    LEFT JOIN returns all rows on the left side and any matching rows on the
    right side. If there are no matching rows the right side values are
    filled in with nulls.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. Replies: 2
    Last Post: September 18th, 09:59 PM
  3. outer join, cross join, union? DDL/sample data incl.
    By Kevin in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 5th, 08:01 PM
  4. Rewrite Query using Inner Join, Left Join e.t.c
    By Vishal in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 23rd, 08:02 AM
  5. Select Left Join AND Right Join
    By David in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 15th, 03:42 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139