Sorting Results From Two Tables

CarlosinFL · #1 (**permalink**) 10-07-11, 11:30

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...

r937 · #2 (**permalink**) 10-07-11, 11:52

Code:

SELECT users.user_id
     , users.fname
     , users.lname 
     , orders.order_id
  FROM users
INNER
  JOIN orders
    ON orders.user_id = users.user_id

CarlosinFL · #3 (**permalink**) 10-07-11, 12:45

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!

r937 · #4 (**permalink**) 10-07-11, 12:52

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

JarlH · #5 (**permalink**) 10-10-11, 02:38

Quote:

Originally Posted by r937

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech