Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25

    Unanswered: how do u combine two unrelated tables that have one-to-many relationships in query

    Hello everyone.

    i am trying to combine several unrelated tables in a mysql query; the tables and each in a one-to-many relationships.

    i have tried to draft the query myself but it resulted in a cross join effects

    i am aware that a UNION OR A UNION ALL is used to combine unrelated tables. however i read that in order to use these types, the data must have the same number of columns and MUST also comprise similar data types

    my table do not match this. so , i am unclear how to join them.

    i suspect that i might have to do a subquery on my join clause, but i am not sure whether this is the correct approach.

    i enclose below a copy of my query and of the tables.

    any advice/assistance would be greatly appriciated.

    THE QUERY;
    PHP Code:

    function the_worker_profile (     )
        
        
         {             
                 global 
    $dbc;
                          
        
        
    $select "      SELECT 
                       users.first_name ,
                     GROUP_CONCAT(thatlang.language) AS other_languages,
                      GROUP_CONCAT( wgi.profile)As gen_profile
                    
                    " 

             
                  
        
    $from   "     FROM 
                workers_languagelist as thislangspoken
                         
                INNER
                            JOIN users
                             ON users.user_id = thislangspoken.user_id
                LEFT OUTER
                              JOIN workers_languagelist AS thatlangspoken      
                                ON thatlangspoken.user_id = users.user_id
                                AND thatlangspoken.language_id <> thislangspoken.language_id
                LEFT OUTER
                                JOIN languagelist AS thatlang
                                ON thatlang.language_id = thatlangspoken.language_id
                
                                
                          LEFT OUTER JOIN                     
                workers_general_profiles as workersprofile
                           on  workersprofile.user_id =  users.user_id 
                      LEFT OUTER JOIN  workers_general_profiles_list wgi
                        ON     wgi.profile_id =  workersprofile.profile_id          
                  
                                                  
                        "
    ;
              
        
                                    
                       
                     
    $where  "   WHERE             thislangspoken.language_id = 18 "

            
    $GROUPBY   "     GROUP
                    BY  users.first_name "
    ;
                           
                  
    $order=  "     ORDER BY  users.first_name DESC  ";  
             
            
    $query $select.$from.$where$GROUPBY$order ;
             
             
    $result = @mysqli_query ($dbc$query)or trigger_error("Query: $query\n<br />MySQL Error: " mysqli_error($dbc))    ;
            
             return 
    $result 
             
             
        }

    //i now enclose the tables that went into this query

    //General table of members names
    USE workers;
    CREATE TABLE user(
    user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    membership_type CHAR(3NOT NULL
    first_name VARCHAR(30NOT NULL,
    last_name VARCHAR(40NOT NULL,

    )
    /
    the table below is the link table.i.e the profile id and the profile name.

    USE 
    workers;
    CREATE TABLE workers_general_profiles_list(
    profile_id MEDIUMINT UNSIGNED NOT NULL,
    profile VARCHAR(60NOT NULL,
    INDEX profile_id (profile_id profile),
    INDEX profileprofile profile_id ),
    UNIQUE KEY (profile ) );



    //the table below is the link between the table above and the users table.

    USE workers;
    CREATE TABLE workers_general_profiles(
    profile_id MEDIUMINT UNSIGNED NOT NULL,
    user_id MEDIUMINT UNSIGNED NOT NULL,
    INDEX user_id (user_id profile_id),
    INDEX profile_idprofile_id user_id )

    );

    //the tables below are the language tables, this part of the query works

    USE workers ;
    CREATE TABLE languagelist(
    language_id MEDIUMINT UNSIGNED NOT NULL
    language VARCHAR(60NOT NULL,
    INDEX language_id language_idlanguage ), 
    INDEX languagelist (languagelanguage_id ), 
    UNIQUE KEY (language)


    ); 



    USE 
    workers ;
    CREATE TABLE workers_languagelist(
    language_id MEDIUMINT UNSIGNED NOT NULL
    user_id MEDIUMINT UNSIGNED NOT NULL,
    INDEX language_idlanguage_iduser_id ), 
    INDEX user_id user_id,language_id


    ); 
    When i tried to combine the unrelated tables it caused cross join effects

    warm regards


    andreea

  2. #2
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Depending on exactly wha you want to do, you might consider selecting the columns you want from both tables into temporary tables and then join them.

    If there are any columns that have different data types, resolve this when inserting into the temporary tables.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andreea115 View Post
    When i tried to combine the unrelated tables it caused cross join effects
    yeah

    you also said "i will now have to read up on that and try and find a way around it" but instead of reading up on it you're trying your luck on a different site

    tsk, tsk

    here is background for anyone that wants to jump in -- Help with my sql query - Dev Shed

    note: storing query results in temporary tables is not necessary, not if you use subqueries properly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    note: storing query results in temporary tables is not necessary, not if you use subqueries properly
    Agree.

    My thought was that breaking this into pieces, might make it easier for andreea to visualize/implement.

    Once understood and working, the temporary tables might be removed and the code converted to subqueries.

  5. #5
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25
    hi everyone.

    sorry to come back again on this same issue ( i am sure that everyone is bored by now).

    please bear with me ; i taught myself CSS, SEO, PHP, MySQL and Jquery.

    i am not looking for someone to just give me the codes; i actually want to advance my MySQL skills and learn to do it myself ; so, (if you have time) please spare me a few moments and explain to me where i am going wrong.

    below is my attempt to replicate the sql statement, originally written by r937; this time however, i tried to replicate it on a a different set of tables. it produced incorrect results.

    it might help if i just summerize what i did;

    i have three tables;

    1. table 1: user ( this is the table with the Id of the users)
    2. table 2: workers_general_profiles_list ( this is the table that lists the categories of the worker groups versus the Id for that group; i.e male = 1 ( id of 1); female = 2 (id of 2) ; age group; 18-25 = 3 (id of 3) , 25-32 = 4(id 4)etc )
    3. table 3: workers_general_profiles ( this is the Id of the users, plus the id of the diffrent categories that the user qualifies for. eg. a user with an Id of 1 might have a workers_general_profiles_list id of 2 (female) and a workers_general_profiles_list id of 3(between the age of 18-25). etc.


    i have enclosed a copy of my tables below;

    accordingnly, in order to find out the age group and gender of a user i.e GROUP_CONCAT this info, i did the following;

    i first did an inner join of the id's of the users (table 1)with the Id's of the workers_general_profiles(table 3) .

    this produced the Id's of all the workers_general_profiles (table 3) which incidentially is the same as those id's in table 2;

    i then did a further inner join in which i linked the id's of workers_general_profiles (table 3) with the id's of workers_general_profiles_list (table 2).

    this should produce the names of the categories that the user fits into; i.e male, the age group the worker fits into etc.

    please tell me whether this was the correct approach thus far;

    i enclose below a copy of my query;

    PHP Code:

    function the_worker_profile (     )
         {             
                 global 
    $dbc;

        
    $select "      SELECT 
                users.user_id, 
                       users.first_name ,
                       GROUP_CONCAT( wgi.profile)As gen_profile
                    " 

             
        
    $from   "     

                             FROM 
                workers_general_profiles as thisworkersprofile
                INNER JOIN users
                               ON users.user_id = thisworkersprofile.user_id
                          LEFT OUTER
                              JOIN workers_general_profiles AS thatworkersprofile   
                           on  thatworkersprofile.user_id =  users.user_id 
                      LEFT OUTER JOIN  workers_general_profiles_list wgi
                        ON     wgi.profile_id = thatworkersprofile.profile_id          
                  

                        "
    ;
              
                     
    $where  "   WHERE            users.live = 1 "

            
    $GROUPBY   "     GROUP
                 BY  users.user_id"
    ;
                           
                  
    $order=  "     ORDER BY  users.first_name DESC  ";  
             
            
    $query $select.$from.$where$GROUPBY  .$order ;
             
             
    $result = @mysqli_query ($dbc$query)or trigger_error("Query: $query\n<br />MySQL Error: " mysqli_error($dbc))    ;
            
             return 
    $result 
             
             
        } 

    BELOW ARE MY TABLES;

    PHP Code:

    USE worker;
    CREATE TABLE users(
    user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    membership_type CHAR(3NOT NULL
    first_name VARCHAR(30NOT NULL

     
    ); 



    USE 
    worker;
    CREATE TABLE workers_general_profiles_list(
    profile_id MEDIUMINT UNSIGNED NOT NULL
    profile VARCHAR(60NOT NULL,
    INDEX profile_id (profile_id profile), 
    INDEX profileprofile profile_id ), 
    UNIQUE KEY (profile ) ); 





    USE 
    worker;
    CREATE TABLE workers_general_profiles(
    profile_id MEDIUMINT UNSIGNED NOT NULL
    user_id MEDIUMINT UNSIGNED NOT NULL
    INDEX user_id (user_id profile_id), 
    INDEX profile_idprofile_id user_id 

    ); 
    thank you very much for your kind attention and i look forward to your responce.

    warm regards

    Andreea 115

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andreea115 View Post
    please tell me whether this was the correct approach thus far;
    yes, that's the correct approach, with one exception

    in order to develop a GROUP BY query, you should first develop the detailed query without the GROUP BY

    also, you should test your queries right in mysql, not from php

    removing the GROUP BY from your query, it will look like this --
    Code:
    SELECT users.user_id
         , users.first_name  
         , thisworkersprofile.profile_id AS this_profile_id    
         , thatworkersprofile.profile_id AS that_profile_id    
         , wgi.profile AS that_profile  FROM users
    INNER 
      JOIN workers_general_profiles as thisworkersprofile
        ON thisworkersprofile.user_id = users.user_id 
    LEFT OUTER
      JOIN workers_general_profiles AS thatworkersprofile   
        ON thatworkersprofile.user_id =  users.user_id 
    LEFT OUTER 
      JOIN workers_general_profiles_list wgi
        ON wgi.profile_id = thatworkersprofile.profile_id    
     WHERE users.live = 1
    ORDER 
        BY users.first_name DESC
    run that, and you will be able to see the results of your joins

    the obvious question here is why did you join to workers_general_profiles twice?

    that's the exception to your approach
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25
    Hello again everyone,

    and special thanks again to R937. ( i have learnt a lot about mysql queries from his assistance)

    i ran the query and it worked perfectly; ( in answer to his question of why i enclosed the same table twice; this was a silly mistake on my part)so thank again for that.

    i really just have one final question.

    Re: how do i now combine/ expand upon the query to include another one-to-many query on an unrelated table.


    R937 stated that i should use subqueries ( thus avoiding cross join).
    the question that i now have is this;

    where do i run the subquery from?. i read up on subqueries but my understanding is that they normally take place from the "WHERE CLAUSE" (not from the FROM CLAUSE) ;

    it appears to me that , regarding our present query, there are only two possible places that it can be run from; the SELECT PART or the FROM CLAUSE

    i enclose below my attempt to run the subqueries;
    PHP Code:
    SELECT 
                    users
    .user_id
                            
    users.first_name  
                          
    ,  thisworkersprofile.profile_id AS this_profile_id    
                            
    GROUP_CONCATwgi.profile) AS that_profile 
                             
    GROUP_CONCATthatlang.language)As gen_profile
                         
                  FROM users
                   INNER JOIN workers_general_profiles 
    as thisworkersprofile
                        ON thisworkersprofile
    .user_id users.user_id 
                   LEFT OUTER JOIN workers_general_profiles_list wgi
                      ON wgi
    .profile_id thisworkersprofile.profile_id 
           
    LEFT OUTER JOIN   workers_languagelist 
    as thislangspoken 
     

                             
    ON users.user_id thislangspoken.user_id
                LEFT OUTER
                              JOIN workers_languagelist 
    AS thatlangspoken      
                                ON thatlangspoken
    .user_id users.user_id
                                
    AND thatlangspoken.language_id <> thislangspoken.language_id
                LEFT OUTER
                                JOIN languagelist 
    AS thatlang
                                ON thatlang
    .language_id thatlangspoken.language_id                              
               
    )

            
    WHERE            users.live 
          GROUP
                 BY  users
    .user_id
                          
                   ORDER BY  users
    .first_name DESC 
    any advice/suggestions would be really appriciated

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's not a subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25
    Hello again R937

    thank you for your reply. i am a bit confused. can you please explain why its not a subquery; do you mean its not a subquery because i tried to run it from the wrong place.

    warm regards

    Andreea 115

  10. #10
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25
    Hello again everyone

    re;

    Quote Originally Posted by r937 View Post
    that's not a subquery
    special thanks to R937 for his help.

    as i previouly said; i am at a complete loss as to why my query is not a subquery.; did i place my subquery in the wrong place; should it have been placed in the SELECT SECTION?.

    i was wondering whether anyone could please show me a CODE example of a query involving several different table of which many of them are in a one-to- many relationship with other tables.

    i searched online for an example of this but could not find anything.

    i enclose again a copy of my incorrect query:

    Re:


    PHP Code:
    SELECT 
                    users
    .user_id
                            
    users.first_name  
                          
    ,  thisworkersprofile.profile_id AS this_profile_id    
                            
    GROUP_CONCATwgi.profile) AS that_profile 
                             
    GROUP_CONCATthatlang.language)As gen_profile
                         
                  FROM users
                   INNER JOIN workers_general_profiles 
    as thisworkersprofile
                        ON thisworkersprofile
    .user_id users.user_id 
                   LEFT OUTER JOIN workers_general_profiles_list wgi
                      ON wgi
    .profile_id thisworkersprofile.profile_id 
           
    LEFT OUTER JOIN   workers_languagelist 
    as thislangspoken 
     

                             
    ON users.user_id thislangspoken.user_id
                LEFT OUTER
                              JOIN workers_languagelist 
    AS thatlangspoken      
                                ON thatlangspoken
    .user_id users.user_id
                                
    AND thatlangspoken.language_id <> thislangspoken.language_id
                LEFT OUTER
                                JOIN languagelist 
    AS thatlang
                                ON thatlang
    .language_id thatlangspoken.language_id                              
               
    )

            
    WHERE            users.live 
          GROUP
                 BY  users
    .user_id
                          
                   ORDER BY  users
    .first_name DESC 

Posting Permissions

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