Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Question Unanswered: Sorting Results From Two Tables

    I'm trying to get data from a database but I'm not sure how to achieve results pulling from two separate tables in my database:

    I'm trying to return results from the 'users' table (user_id, fname, lname) & their (order_id) from the 'orders' table [b]only for 'users' that have placed an order in the 'orders' table. Can someone help me with how this ANSI SQL SELECT statement would appear?

    Right now I only know the following:

    Code:
    SELECT user_id, fname, lname FROM users...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT users.user_id
         , users.fname
         , users.lname 
         , orders.order_id
      FROM users
    INNER
      JOIN orders
        ON orders.user_id = users.user_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Question

    Thanks that worked beautiful!

    Code:
    mysql> SELECT users.userId, firstname, lastname, orders.orderId FROM users INNER JOIN orders ON orders.userId = users.userId;+--------+-----------+----------+---------+
    | userId | firstname | lastname | orderId |
    +--------+-----------+----------+---------+
    |      1 | John      | Doe      |       1 | 
    |      1 | John      | Doe      |       2 | 
    |      2 | Jane      | Smith    |       3 | 
    |      2 | Jane      | Smith    |       4 | 
    |      3 | Sally     | Jones    |       5 | 
    |      4 | Jack      | Dole     |       6 | 
    |      4 | Jack      | Dole     |       7 | 
    |      4 | Jack      | Dole     |       8 | 
    |      4 | Jack      | Dole     |       9 | 
    |      4 | Jack      | Dole     |      10 | 
    |      4 | Jack      | Dole     |      11 | 
    |      5 | Sam       | Jackson  |      12 | 
    |     17 | Chris     | Gonzalez |      13 | 
    +--------+-----------+----------+---------+
    13 rows in set (0.00 sec)
    I also played with this statement using the DISTINCT & GROUP BY statements.

    Code:
    mysql> SELECT DISTINCT(users.userId), firstname, lastname FROM users INNER JOIN orders ON orders.userId = users.userId GROUP BY users.userId;
    +--------+-----------+----------+
    | userId | firstname | lastname |
    +--------+-----------+----------+
    |      1 | John      | Doe      | 
    |      2 | Jane      | Smith    | 
    |      3 | Sally     | Jones    | 
    |      4 | Jack      | Dole     | 
    |      5 | Sam       | Jackson  | 
    |     17 | Chris     | Gonzalez | 
    +--------+-----------+----------+
    6 rows in set (0.00 sec)
    Thank you very much for showing me how this works!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    regarding your first query, you should ~not~ remove table qualifiers from the columns in the query i gave you -- best practice is to qualify ~all~ columns in any query which involves more than one table

    regarding your second query, DISTINCT is ~not~ a function, so please do not put parentheses around the first column that comes after the DISTINCT keyword

    also, if you use GROUP BY, then DISTINCT is redundant
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Quote Originally Posted by r937 View Post
    regarding your first query, you should ~not~ remove table qualifiers from the columns in the query i gave you -- best practice is to qualify ~all~ columns in any query which involves more than one table

    regarding your second query, DISTINCT is ~not~ a function, so please do not put parentheses around the first column that comes after the DISTINCT keyword

    also, if you use GROUP BY, then DISTINCT is redundant
    And when GROUP BY each column reference in the SELECT list must either identify a grouping column or be the argument of a set function.

Posting Permissions

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