SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how can you count() all iterations of a join table?

    I have three tables:

    TABLE1
    t1_id
    t1_name

    TABLE2
    t2_id
    t2_name

    TABLE3
    t1_id
    t2_id

    I need to do the following in ONE query if possible. Find all t2_names and their COUNT() WHERE t1_id = 1, or whatever I need t1 to equal.

    Can that be done in query or is it multiple queries?

    Thanks.

  2. #2
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Untested but try this:
    Code:
        SELECT *,count(t2_id) AS count
          FROM t2
          LEFT JOIN t1 ON
        	t2.t2_id = t1.t1_id
          WHERE t1.t1_id = 1
    (at least something to that effect)
    Aaron Brazell
    Technosailor



  3. #3
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Sketch
    Untested but try this:
    Code:
        SELECT *,count(t2_id) AS count
          FROM t2
          LEFT JOIN t1 ON
        	t2.t2_id = t1.t1_id
          WHERE t1.t1_id = 1
    (at least something to that effect)
    I'm pretty sure you have to add a GROUP BY clause in there

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, actually, you don't need a GROUP BY since all the possible rows would belong to only one t1.t1_id=1

    what's actually wrong here is that you can't list the t2 names as well as giving a count (well, you can, but that's even more complex)

    also, it would be t1 left outer join t2, not the other way around

    also, you still want a count if t1.t1_id=1 has no matching t2s, so you would use count(t2.t2_id) instead of count(*)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    in case nobody (myself especially) can now see where that puts us, let me summarize
    Code:
    select count(t2.t2_id) as matches
      from t1
    left outer
      join t3
        on t1.t1_id = t3.t1_id
    left outer
      join t2
        on t3.t2_id = t2.t2_id    
     where t1.name = 'fred'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •