CODEWITHSUNDEEP
mysql
TheBounder
TheBounder

Reputation: 427

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 BY 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?

Upvotes: 0

Views: 1806

Answers (3)

JohnC
JohnC

Reputation: 499

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

Upvotes: 0

ajreal
ajreal

Reputation: 47331

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

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

Upvotes: 2

Ivan
Ivan

Reputation: 3655

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

Upvotes: 0

Related Questions