Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2004
    Location
    SA
    Posts
    4

    Arrow Unanswered: Result from 2 joined tables

    Hi everybody ..

    I have two tables and want to join them in a query to produce a summary report.
    Code:
    Table_1
    ====================
    article_id       | author
    ====================
    10	   | Tom
    11	   | Dave
    12	   | Adam
    13	   | Peter
    ====================
    
    Table_2
    ====================
    article_id       | category
    ====================
    10	   | Ethics
    10	   | Social
    11	   | Sports
    12	   | Entertainment
    12	   | Jokes
    12	   | Amusing
    13	   | Health
    ====================
    The Query is :
    Code:
    SELECT Table_1.article_id, Table_1.author, Table_2.category 
    FROM Table_1, Table_2 
    WHERE Table_1.article_id=Table_2.article_id 
    ORDER BY article_id;
    The result will be :
    Code:
    ==========================================
    article_id	| author	| category
    ==========================================
    10	| Tom  	| Ethics
    10	| Tom  	| Social
    11	| Dave 	| Sports
    12	| Adam 	| Entertainment
    12	| Adam 	| Jokes
    12	| Adam 	| Amusing
    13	| Peter	| Health
    ==========================================
    Actually I need to eleminate those duplicated rows and combine their categories to produce such result like this :
    Code:
    ==============================================
    article_id	| author	| category
    ==============================================
    10	| Tom	| Ethics, Social
    11	| Dave	| Sports
    12	| Adam	| Entertainment, Jokes, Amusing
    13	| Peter	| Health
    ==============================================
    So any idea of how to do this in MySQL (without using PHP, Perl ..etc.) ?
    I'm using MySQL 3.23.39

    Thanks

    Mishal
    Last edited by Mishal; 04-19-04 at 03:30.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you cannot

    you can use the GROUP_CONCAT function (very nice!) in 4.1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    SA
    Posts
    4
    Hmm .. Unfortunately the server configuration is not on my hands .. okay .. thanks Rudy for the valuable advice

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Haven't tested this, but I think it might work:
    PHP Code:
    CREATE TABLE table_1 (
       
    article_id        INT
    ,  author        VARCHAR(20)
       )

    CREATE TABLE table_2 (
       
    article_id        INT
    ,  category        VARCHAR(20)
      )

    INSERT INTO table_1 (article_idauthor)
       
    SELECT 10'Tom'
       
    UNION ALL SELECT 11'Dave'
       
    UNION ALL SELECT 12'Adam'
       
    UNION ALL SELECT 13'Peter'

    INSERT INTO Table_2 (article_idcategory)
       
    SELECT           10'Ethics'
       
    UNION ALL SELECT 10'Social'
       
    UNION ALL SELECT 11'Sports'
       
    UNION ALL SELECT 12'Entertainment'
       
    UNION ALL SELECT 12'Jokes'
       
    UNION ALL SELECT 12'Amusing'
       
    UNION ALL SELECT 13'Health'

    SELECT a.article_ida.author
    ,  Min(b.category
    +  
    Coalesce(', ' Min(c.category), ''
    +  
    Coalesce(', ' Min(d.category), '')
       
    FROM table_1 AS a
       JOIN table_2 
    AS b
          ON 
    (b.article_id a.article_id)
       
    LEFT JOIN table_2 AS c
          ON 
    (c.article_id a.article_id
          
    AND b.category c.category)
       
    LEFT JOIN table_2 AS d
          ON 
    (c.article_id a.article_id
          
    AND c.category d.category)
       
    LEFT JOIN table_2 AS e
          ON 
    (c.article_id a.article_id
          
    AND d.category e.category)
       
    GROUP BY a.article_ida.author
       ORDER BY a
    .article_ida.author

    DROP TABLE table_1
    DROP TABLE table_2 
    -PatP

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sweet

    fyi, pat, in mysql concatenation is accomplished with a function, not an operator
    PHP Code:
    create table table_1
    article_id tinyint
    author varchar(20)
    );
    insert into table_1 values
      
    10'Tom' )
    , ( 
    11'Dave' )
    , ( 
    12'Adam' )
    , ( 
    13'Peter' )
    ;
    create table table_2
    article_id tinyint
    category varchar(20)
    );
    insert into table_2 values
      
    10'Ethics' )
    , ( 
    10'Social' )
    , ( 
    11'Sports' )
    , ( 
    12'Entertainment' )
    , ( 
    12'Jokes' )
    , ( 
    12'Amusing' )
    , ( 
    13'Health' )
    ;
    select a.article_id
         
    a.author
         
    CONCAT(min(b.category
             , 
    coalesce(CONCAT(', ',min(c.category)), ''
             , 
    coalesce(CONCAT(', ',min(d.category)), '')
                 ) as 
    PatPstring
      from table_1 
    as a
    inner
      join table_2 
    as b
        on a
    .article_id b.article_id
    left outer
      join table_2 
    as c
        on a
    .article_id c.article_id
       
    and b.category c.category
    left outer
      join table_2 
    as d
        on a
    .article_id c.article_id
       
    and c.category d.category
    left outer
      join table_2 
    as e
        on a
    .article_id c.article_id
       
    and d.category e.category
    group 
        by a
    .article_id
         
    a.author
    order 
        by a
    .article_id
         
    a.author

    article_id  author  PatPstring
      10        Tom     Ethics
    SocialSports
      11        Dave    Sports
      12        Adam    Amusing
    EntertainmentEthics
      13        Peter   Health 
    as you can see from the results, your query doesn't work

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My bad. I was typing on the fly and couldn't test at all (actually I still can't test, this is "from the hip"). I'd like to take another shot using:
    PHP Code:
    CREATE TABLE table_1 (
       
    article_id        INT
    ,  author        VARCHAR(20)
       )

    CREATE TABLE table_2 (
       
    article_id        INT
    ,  category        VARCHAR(20)
      )

    INSERT INTO table_1 (article_idauthor)
       
    SELECT           10'Tom'
       
    UNION ALL SELECT 11'Dave'
       
    UNION ALL SELECT 12'Adam'
       
    UNION ALL SELECT 13'Peter'

    INSERT INTO Table_2 (article_idcategory)
       
    SELECT           10'Ethics'
       
    UNION ALL SELECT 10'Social'
       
    UNION ALL SELECT 11'Sports'
       
    UNION ALL SELECT 12'Entertainment'
       
    UNION ALL SELECT 12'Jokes'
       
    UNION ALL SELECT 12'Amusing'
       
    UNION ALL SELECT 13'Health'

    SELECT a.article_ida.author
    ,  Min(b.category
    +  
    Coalesce(Concat(', 'Min(c.category)), ''
    +  
    Coalesce(Concat(', 'Min(d.category)), '')
    +  
    Coalesce(Concat(', 'Min(e.category)), '')
       
    FROM table_1 AS a
       JOIN table_2 
    AS b
          ON 
    (b.article_id a.article_id)
       
    LEFT OUTER JOIN table_2 AS c
          ON 
    (c.article_id a.article_id
          
    AND b.category c.category)
       
    LEFT OUTER JOIN table_2 AS d
          ON 
    (d.article_id a.article_id
          
    AND c.category d.category)
       
    LEFT OUTER JOIN table_2 AS e
          ON 
    (e.article_id a.article_id
          
    AND d.category e.category)
       
    GROUP BY a.article_ida.author
       ORDER BY a
    .article_ida.author

    DROP TABLE table_1
    DROP TABLE table_2 
    -PatP
    Last edited by Pat Phelan; 04-19-04 at 18:27.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ran your query

    got these results:

    10,Tom,0
    11,Dave,0
    12,Adam,0
    13,Peter,0

    know why? you still gots some plus signs in your query

    mysql takes you literally



    you can keep trying, i'll leave my MySQL-Front session open, it's a simple copy/paste and F9 to run it...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Blast! When are they going to get MySQL running on the Palm so I can actually run while "on the run" ?!?! Please try the edited code again.

    -PatP

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, fixing up the concat...

    SELECT a.article_id, a.author
    , CONCAT(
    Min(b.category)
    , Coalesce(Concat(', ', Min(c.category)), '')
    , Coalesce(Concat(', ', Min(d.category)), '')
    , Coalesce(Concat(', ', Min(e.category)), '')
    ) as PatPstring
    FROM ...


    results:
    PHP Code:
    10   Tom    EthicsSocialSports
    11   Dave   Sports
    12   Adam   Amusing
    EntertainmentEthicsHealth
    13   Peter  Health 
    still not right

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Did you get the other fixes that I made?

    -PatP

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yep

    just ran your last (edited) query again to be sure

    by the way, it's really slow

    > 1 sec

    which is *ages* for a trivial test-case query


    where's brettk telling us we have to use a cursor?

    oh, it's after 5:00...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Dang, one set of changes didn't stick. Could you try it again please?

    -PatP

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    looks like this does the job!!
    PHP Code:
    SELECT a.article_ida.author
    ,  CONCAT(
        
    Min(b.category
        , 
    Coalesce(Concat(', 'Min(c.category)), ''
        , 
    Coalesce(Concat(', 'Min(d.category)), '')
        , 
    Coalesce(Concat(', 'Min(e.category)), '')
             ) as 
    PatPstring
      FROM table_1 
    AS a
       JOIN table_2 
    AS b
          ON 
    (b.article_id a.article_id)
       
    LEFT OUTER JOIN table_2 AS c
          ON 
    (c.article_id a.article_id
          
    AND b.category c.category)
       
    LEFT OUTER JOIN table_2 AS d
          ON 
    (d.article_id a.article_id
          
    AND c.category d.category)
       
    LEFT OUTER JOIN table_2 AS e
          ON 
    (e.article_id a.article_id
          
    AND d.category e.category)
       
    GROUP BY a.article_ida.author
       ORDER BY a
    .article_ida.author


    10   Tom    Ethics
    Social
    11   Dave   Sports
    12   Adam   Amusing
    EntertainmentJokes
    13   Peter  Health 
    nice one, pat
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Apr 2004
    Location
    SA
    Posts
    4
    Oh my GOD! , thats really sweet from you guys

    I ran this query but it gives an error message :
    Code:
    Error Code : 1064
    You have an error in your SQL syntax near 'ON (b.article_id = a.article_id)
       LEFT OUTER JOIN table_2 AS c
          ON (c.art' at line 10
    (0 ms taken)
    i think this is a version issue.

    If I couldn't do it in one query then i will create a temporary table for storing and concatenating these categories in every time the table_2 getting updated.

    Thanks Pat for re-ignite this subject , Rudy .. i appreciate your help .

    Mishal

  15. #15
    Join Date
    Apr 2004
    Location
    SA
    Posts
    4
    There is some one suggest the following way using subqueries method (not tested) :
    PHP Code:
    SELECT distinct table_1.article_idtable_1.author
       (
    select distinct table_2.category from table_2 
    where table_1
    .article_id table_2.article_id) as 'category' 
    FROM table_1 ORDER BY table_1.article_id
    But still not working in v3.23x Uhh !
    Last edited by Mishal; 04-20-04 at 08:24.

Posting Permissions

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