Selecting all records except the very first

I have a table which holds a customer and another table which holds results from that customer.

One customer can have many results.

I want to be able to firsly select only the customers which have more than one entry in CustResults, and then from those, I want all the records except the first one...

I have this so far, which retrieves the customers with more than one result, but I don't know how to then ingore then first result.

SELECT * FROM CustResults cp 
JOIN Customer c ON c.CustomerID = cp.CustomerID
WHERE
(SELECT count(CustomerID) as cpid 
 FROM CustResults WHERE CustomerID = cp.CustomerID GROUP B开发者_开发技巧Y CxID) > 1

i.e.

Rita: RESULT 1 Sue: RESULT 1, Result 2, Result 3, Result 4 Bob: RESULT 1, Result 2, Result 3

I only want Sue and Bob, as Rita only has one result, and from Sue and Bob, I only want to look at results 2,3,4

Any ideas?

Thanks

ADDED MORE INFO:

Here is my exact query:

SELECT count(cp.CxID) as intSmokers FROM CustPrimarySmoking cp JOIN Customer c ON cp.CxID = c.CustomerID WHERE (SELECT count(CustPrimarySmokingID) as cqpid FROM CustPrimarySmoking WHERE CxID = cp.CxID GROUP BY CxID) > 1

Obviously, I can just use LIMIT 1, 99999, because the query is only returning one value (the count).

I want the count to be using the customers with more than one record in CustPrimarySmoking, but ignoring the first entry.

Any futher ideas?

---

Stupid and not-so flexible (however, it should works all most of the time) ...

LIMIT 1, 999999999;
         ^ to ensure all rows are returned

---

Have you tried to add "OFFSET 1" at the end of the query?

---

If all you are after is the count and not the actual records then you just need to subtract the number of customers from the count you already have (as you have already ascertained that each as at least one record) i.e.

SELECT count(cp.CxID)-count(DISTINCT cp.CxID) as intSmokers
FROM CustPrimarySmoking cp
JOIN Customer c ON cp.CxID = c.CustomerID
WHERE (
  SELECT count(CustPrimarySmokingID) as cqpid
  FROM CustPrimarySmoking WHERE CxID = cp.CxID
  GROUP BY CxID
  ) > 1

If however you are after the actual rows, how about this:

    SELECT * FROM Customer;
    +------------+------+
    | CustomerID | name |
    +------------+------+
    |          1 | Rita |
    |          2 | Sue  |
    |          3 | Bob  |
    |          4 | Jack |
    +------------+------+


    SELECT * FROM CustPrimarySmoking;

    +----------------------+------+-------------------+
    | CustPrimarySmokingID | CxID | result            |
    +----------------------+------+-------------------+
    |                    1 |    1 | Result 1 for Rita |
    |                    2 |    2 | Result 1 for Sue  |
    |                    3 |    2 | Result 2 for Sue  |
    |                    4 |    2 | Result 3 for Sue  |
    |                    5 |    2 | Result 4 for Sue  |
    |                    6 |    3 | Result 1 for Bob  |
    |                    7 |    3 | Result 2 for Bob  |
    |                    8 |    3 | Result 3 for Bob  |
    +----------------------+------+-------------------+

    SELECT * FROM CustPrimarySmoking cp
    JOIN Customer c ON cp.CxID = c.CustomerID
    WHERE CustPrimarySmokingID <> (
        SELECT CustPrimarySmokingID
        FROM CustPrimarySmoking
        WHERE CxID = cp.CxID ORDER BY CustPrimarySmokingID LIMIT 1
    );


    +----------------------+------+------------------+------------+------+
    | CustPrimarySmokingID | CxID | result           | CustomerID | name |
    +----------------------+------+------------------+------------+------+
    |                    3 |    2 | Result 2 for Sue |          2 | Sue  |
    |                    4 |    2 | Result 3 for Sue |          2 | Sue  |
    |                    5 |    2 | Result 4 for Sue |          2 | Sue  |
    |                    7 |    3 | Result 2 for Bob |          3 | Bob  |
    |                    8 |    3 | Result 3 for Bob |          3 | Bob  |
    +----------------------+------+------------------+------------+------+