SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    INNER JOIN question

    Hi

    I have the following INNER JOIN in one of my select queries, but it only fetches the ID of the industry. I need to get the industry name which is stored in another table ("industries"). How can I do that?

    Code:
    INNER JOIN (
    	SELECT user_id, group_concat( industry_id
    	SEPARATOR ',' ) AS industry
    	FROM users_industries
    	GROUP BY user_id
    ) AS i ON i.user_id = users.id
    Thanks in advance.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    join the two tables

  3. #3
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    join the two tables
    using a left join within the inner join?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Zaggs View Post
    using a left join within the inner join?
    by asking this question, you reveal either mistrust of your own data, or misunderstanding of outer joins

    i'm going to guess that there won't be any industry_id values in the user_industries table that don't also exist in the industries table, and therefore an outer join is not warranted

    that guess will be an absolute certainty if you have actually declared foreign keys in the user_industries table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard Zaggs's Avatar
    Join Date
    Feb 2005
    Posts
    1,051
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    by asking this question, you reveal either mistrust of your own data, or misunderstanding of outer joins

    i'm going to guess that there won't be any industry_id values in the user_industries table that don't also exist in the industries table, and therefore an outer join is not warranted

    that guess will be an absolute certainty if you have actually declared foreign keys in the user_industries table
    Here is the whole query:. I'm at a loss of how to make this happen

    Code:
    SELECT 
    	users.id, 
    	users.first_name, 
    	users.last_name, 
    	users.dob, 
    	users.address_1, 
    	users.address_2, 
    	users.city, 
    	users.county AS country, 
    	users.telephone, 
    	users.email, 
    	users.username, 
    	users.language, 
    	i.industry, 
    	j.job_type, 
    	users.cv
    
    FROM users
    
    INNER JOIN (
    	SELECT user_id, group_concat( industry_id
    	SEPARATOR ',' ) AS industry
    	FROM users_industries
    	GROUP BY user_id
    ) AS i ON i.user_id = users.id
    
    INNER JOIN (
    	SELECT user_id, group_concat( job_type_id
    	SEPARATOR ',' ) AS job_type
    	FROM users_job_types
    	GROUP BY user_id
    ) AS j ON j.user_id = users.id
    
    ORDER BY users.signup_date DESC

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    INNER JOIN (
      SELECT ui.user_id
           , GROUP_CONCAT( 
                CONCAT(ui.industry_id,';',i.name)
                SEPARATOR ',' ) AS industry
        FROM users_industries AS ui
      INNER
        JOIN industries AS i
          ON i.id = ui.industry_id
      GROUP 
          BY ui.user_id
               ) AS ind ON ind.user_id = users.id
    the subquery now returns a string of ids and names like this --
    Code:
    9;mining,3;farming,7;fishing
    notice the two different separators and where they came from

    you'll probably also want to do the same with job types, yes?

    p.s. you did understand what i said about foreign keys, right?
    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
  •