LEFT JOIN problems

Hey,

I am trying to solve a problem. I have two tables set up, as follows:

customer

+---------+----------+----------+-----------+----------+
| custNum | name     | city     | credLimit | salesNum |
+---------+----------+----------+-----------+----------+
| 2001    | Hoffman  | London   |   1000.00 | 1001     | 
| 2002    | Gioanni  | Rome     |   2000.00 | 1003     | 
| 2003    | Liu      | San Jose |   2000.00 | 1002     | 
| 2004    | Grass    | Berlin   |   3000.00 | 1002     | 
| 2006    | Clemens  | London   |  16000.00 | 1001     | 
| 2007    | Cisneros | San Jose |   3000.00 | 1004     | 
| 2008    | Pereira  | Rome     |   1200.00 | 1007     | 
+---------+----------+----------+-----------+----------+

Code (markup):

custorder

+----------+---------+------------+---------+----------+
| orderNum | amount  | ordDate    | custNum | salesNum |
+----------+---------+------------+---------+----------+
| 3001     |   18.69 | 2007-10-30 | 2008    | 1007     | 
| 3002     | 1900.10 | 2007-10-03 | 2007    | 1004     | 
| 3003     |  767.19 | 2007-10-30 | 2001    | 1001     | 
| 3005     | 5160.45 | 2007-10-03 | 2003    | 1002     | 
| 3006     | 1098.16 | 2007-10-04 | 2008    | 1007     | 
| 3007     |   75.75 | 2007-10-04 | 2004    | 1002     | 
| 3008     | 4723.00 | 2007-10-05 | 2006    | 1001     | 
| 3009     | 1713.23 | 2007-10-04 | 2002    | 1003     | 
| 3010     | 1390.00 | 2007-10-06 | 2004    | NULL     | 
| 3011     | 9891.00 | 2007-10-06 | 2006    | 1001     | 
+----------+---------+------------+---------+----------+

Code (markup):

Now, I want to use a left outer join to list all of the customer names who do not have records in the custorder table - now, I know that currently every customer has a record in custorder, so the result of such a query would be 0, but I still need to perform it. I am horrible with joins, so can anyone help me out? Thanks!

 

I don't think a join would be the best way to do this. If you are just trying to get the rows in custorder that do not have a cooresponding row in the other, I would do this:

SELECT orderNum, amount, ordDate, custNum, salesNum 
WHERE custNum NOT IN (SELECT custNum FROM customer);

Code (markup):

You may have to modify the sql syntax depending on which database you are using, MSSQL, MySQL, etc.

 

Thanks!

I have another problem now, dealing with unions. I can't find any really good examples of unions on the web, so I have just been playing around - but I can't figure it out.

My problem is that I need to use the UNION operator to list the names and credit limits for all customers assigned to salespersons 1001 and 1002. My salesperson table is:

salesperson:

+----------+---------+-----------+------------+
| salesNum | name    | city      | commission |
+----------+---------+-----------+------------+
| 1001     | Peel    | London    |       0.12 | 
| 1002     | Serres  | San Jose  |       0.10 | 
| 1003     | Alexrod | New York  |       0.11 | 
| 1004     | Motika  | London    |       0.14 | 
| 1007     | Rifkin  | Barcelona |       0.10 | 
+----------+---------+-----------+------------+

Code (markup):

What I have right now is:

SELECT salesNum FROM salesperson WHERE salesNum = '1002' OR salesNum = '1001' UNION SELECT name, credLimit FROM customer;

Code (markup):

But that doesn't work:

ERROR 1222 (21000): The used SELECT statements have a different number of columns
mysql> SELECT * FROM salesperson;

Code (markup):

I'm not sure how to use a union in this way, as it seems very unnecessary, but it's what I have to do. Any pointers?

 

If this is an assignment question it's a bit daft as you wouldn't use a union for this problem you'd do it as you have (minus the union). If you are being forced to use a UNION then do a select with one of the salesNum and union it with a select using the other salesNum, a crap way of doing it but it will work.