Hard nut to crack

[spstieng]

Ok - I think this one is for the hard core MySQL'ers.....

I got two tables, COMPANY and CALLINGLIST.

The CALLINGLIST is a list of companies that a sales person is to
call. The list contains all the companies a sales person have been
given, but only displays the companies that has not been called.
Let's just say that there is a status flag called 'Called' / 'Not Called'.

The COMPANY table contains e.g. 50.000 companies.

Now, when the sales person has called all 10 companies, 10 new
companies from the COMPANY table is put into the CALLINGLIST.

PROBLEM STATEMENT

I need to go through the Company list and pick 10 new companies
to be put into the Callinglist, which has yet not ben given to any
sales persons. (I compare the two lists)

How can I only get 10 companies from a select statement?

It won't work using the LIMIT function, because the LIMIT function
starts from a position and reads ALL rows to a given number.

That means if a list has the orgID's 20,30,40 and 50, and they all
have been signed to a sales person, and the list gets updated
with new companies with the numbers 25,35,45 and 55, I would
need to pick these new companies and not just continue counting
from where I ended last time.

Sure I could put new companies last in the list, but I would like to
have the list sorted after company ID's.

Does anyone have a clue what I'm trying to say?? 🙂

[bastien]

Outer joins would work here. If I understand correctly you want the companies from the companies list where it does not exist on the calling list. Right? The left outer join could take all of the records from the company table (the left one) where there is no corresponding ID field in the Calling List.

Hard to provide a sample statement without table structure but it would be something like this:

select company_id, contact_info, phone_number from companies
left outer join calling list on companies.company_id = calling_list.company_id
where
calling_list.company_id is null
[edit] LIMIT 10[/edit]

As to your last point about ordering the list, realize that VISUAL order in a list on the screen does not need to relate to the order of items in the db. SQLs order by statements can take care of that aspect on the fly when showing the new list...

[edit] WHOOPS. hate when that happens[/edit]:eek:

[mtimdog]

And since you only want 10, put a LIMIT 10 on the "end" of the query.