Getting first record from two tables

I am looking to get the first date with the customer and item description from the following example table

To create my example

CREATE TABLE cust
(
CUSTNO int,
ENAME varchar(50)
)

CREATE TABLE orders
(
CUSTNO int,
Description varchar(50),
ORDERDATE date
)

INSERT INTO cust VALUES (7369, 'SMITH');
INSERT INTO cust VALUES (7499, 'ALLEN');
INSERT INTO cust VALUES (7521, 'WARD');
INSERT INTO cust VALUES (7566, 'JONES');
INSERT INTO cust VALUES (7654, 'MARTIN');
INSERT INTO cust VALUES (7698, 'BLAKE');
INSERT INTO cust VALUES (7782, 'CLARK');
INSERT INTO cust VALUES (7788, 'SCOTT');
INSERT INTO cust VALUES (7839, 'KING');
INSERT INTO cust VALUES (7844, 'TURNER');
INSERT INTO cust VALUES (7876, 'ADAMS');
INSERT INTO cust VALUES (7900, 'JAMES');
INSERT INTO cust VALUES (7902, 'FORD');
INSERT INTO cust VALUES (7934, 'MILLER');

INSERT INTO orders VALUES (7782, 'Something','17-DEC-1980');
INSERT INTO orders VALUES (7782, 'Something else', '17-DEC-2000');
INSERT INTO orders VALUES (7900, 'Something', '17-DEC-1980');
INSERT INTO orders VALUES (7900, 'Something else','17-DEC-1990');
INSERT INTO orders VALUES (7934, 'Something','17-DEC-1980');

Was trying something like this

  select [EN开发者_StackOverflow中文版AME],[cust].[CUSTNO], MIN([ORDERDATE]),[Description]
  from [cust],[orders]
  where [cust].[CUSTNO]=[orders].[CUSTNO]
  group by [cust].[CUSTNO],[ENAME],[Description]

My problem is this returns too many rows. I just want to see each customer and then list there first date (blank or null if there is no order).

Any ideas?

---

 with MinOrder as (
  select
  custno, min(orderdate) orderdate
  from [orders]
  GROUP BY custno)

  select [ENAME],[cust].[CUSTNO], o.[orderdate], [Description]
   from 
  cust 
  LEFT JOIN orders o
  ON cust.custNo = o.custno
  LEFT JOiN MinOrder mo
  ON mo.orderdate = o.orderdate
    and mo.custno = o.custno

If you can't use a CTE you can use a inline view instead

 select [ENAME],[cust].[CUSTNO], o.[orderdate], [Description]
   from 
  cust 
  LEFT JOIN orders o
  ON cust.custNo = o.custno
  LEFT JOiN (  select
     custno, min(orderdate) orderdate
  from [orders]
  GROUP BY custno)  mo
  ON mo.orderdate = o.orderdate
    and mo.custno = o.custno

---

  SELECT c.[ENAME],c.[CUSTNO], o.[orderdate], o.[Description] 
   FROM  cust  c
  LEFT JOIN orders o 
    ON c.custNo = o.custno 
  LEFT JOIN 
        ( SELECT   custno, min(orderdate) orderdate 
            FROM [orders] 
            GROUP BY custno)  mo 
    ON mo.orderdate = o.orderdate 
        AND mo.custno = o.custno 

You need the left joins to get the order if one exists but still get the customer if no orders exist. @conrad Frix's solution will work as well, but I incuded this in case you use a database that doesn't accept the with statement.

In the future, you should stop writing implict joins (using a comma to separate the tables and putting the join conditions inteh where clause). They are a bad programming techinique and contribute to why you don't understand joins correctly (or you would have known to use a left join).

---

;with cteMinOrder as (
    select [CUSTNO], MIN([ORDERDATE]) as minDate
        from [orders]
        group by [CUSTNO]
)
select [ENAME],[cust].[CUSTNO], mo.minDate, [Description]
    from [cust]
        left join cteMinOrder mo
            on [cust].[CUSTNO]=mo.[CUSTNO]
        left join [orders]
            on [cust].[CUSTNO]=[orders].[CUSTNO]
                and mo.minDate = [orders].[ORDERDATE]

---

select [ENAME],[cust].[CUSTNO],[minorderdate],[Description]
from 
  [cust]
left join
  select [CUSTNO],MIN([ORDERDATE]) AS minorderdate
  from [orders]
  group by [CUSTNO] as t
on [cust].[CUSTNO] = [t].[CUSTNO];

Syntax might need tweaking depending on the actual dialect you're using, but that relation should do what you want.

---

Just omit [ENAME] and [Description] from the GROUP BY clause