#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    70
    Rep Power
    2

    Wondering about cross Joins


    I am trying to do something I am not sure if it is possible.

    I want to add a couple of queries together. But there wont be results in all columns.

    E.name s.date v.date

    There is only one s.date for each name, there are many v.date for each name.

    So I am getting undesirable cross joins or strange results.

    My query that I am messing with is like so:

    Code:
    SELECT e.name, CONCAT(s._date,s.am_pm) 
    FROM employee as e
    JOIN stat_picks as s
    ON e.employee_id = s.employee_id
    JOIN vacation_picks as v
    ON v.employee_id = e.employee_id
    WHERE e.platoon_id = 1 and e.retired IS NULL
    I am not sure what kind of joins to use. Or if this is even possible?

    Thanks.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,732
    Rep Power
    4288
    Code:
    SELECT e.name
         , s._date
         , s.am_pm
         , GROUP_CONCAT(v.date) AS v_dates 
      FROM employee as e
    INNER
      JOIN stat_picks as s
        ON s.employee_id = e.employee_id
    INNER
      JOIN vacation_picks as v
        ON v.employee_id = e.employee_id
     WHERE e.platoon_id = 1 
       and e.retired IS NULL
    GROUP
        BY e.name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo