SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    table1 table2 => table3 + productnumber

    I need your help guys. I try to find a way to get something like t3 from t1 and t2! I would like to have a table like t3! Please help me out! Iam workin on this query for 2hours and can't find the solution!
    ...

    I have t1 and t2:
    t1
    -productid
    -infos
    ...
    t2
    -id
    -product_id
    -customer_id
    ...

    ..and I need this:
    t3
    -productid
    -number of products in t2 AS counter
    -infos
    ...

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT
      t1.productid,
      (SELECT COUNT(DISTINCT(product_id)) FROM t2) AS `counter`,
      t1.infos
    FROM
      t1
    Now if that's not what you want, you'll have to explain the "number of products in t2 AS counter" part more clearly

  3. #3
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    with the number of products I mean the number of rows where t1.productid = t2.product_id

    example
    t2
    id1 productnr1 customer_345645 ..
    id2 productnr1 customer_335645 ..
    id3 productnr2 customer_234534 ..
    id4 productnr2 customer_343455 ..
    id5 productnr2 customer_334545 ..
    id5 productnr3 customer_33455 ..
    ...
    t3
    id1 productnr1 numrows2 ..
    id2 productnr2 numrows3 ..
    id3 productnr3 numrows1 ..
    id4 productnr4 numrows0 ..
    ...
    I tryed your query but it doesn't work...it gives me the same numrows for every product...

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT
      t1.productid,
      COUNT(t2.id) AS `counter`,
      t1.infos
    FROM
      t1
        LEFT OUTER JOIN
      t2
        ON t1.productid = t2.productid
    GROUP BY 
      t1.productid

  5. #5
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    awesome..it works...you're my guru now A BIG THANKS TO YOU!!!


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
  •