I need to pull a random set of records with a query. I have a table on
the one side called CUSTOMER with a primary key of CustomerID. I have
another table on the many side that is called CALLS and has the foreign
key CustomerID. I

I need a query to pull records randomly from the tables, but exclude
all CustomerID records if only one of the records on the many side meets
the criteria I'm looking for "Not Interested". Other words, I want to
see all the customer and related calls data, but exclude any customer
altogether if they have just one "Not Interested" record.

Help appreciated.
Thanks,
Frank



*** Sent via Developersdex http://www.developersdex.com ***


http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

When you say randomly, you mean need to select like 10 random customers and
thier call history, but only if they are "interested"?

How random does it need to be? can there ever be duplicates?

"Frank Bishop" <fbishop (AT) viper (DOT) com> wrote

"Frank Bishop" <fbishop (AT) viper (DOT) com> wrote


try something like

select a, b, c from FOO where interest='false' order by newid()

Since you didn't provide and DDL, schema hard to come up with the exact
statement.

Needs to be randomized so if 2 staff members pull the same report,
chances are they will see different customers since we have 30k
customers. I think there is a way to lock records in SQL, but I don't
want to get that elaborate just yet. I need to transfer this logic to
Crystal Reports once I understand the SQL. So in my CUSTOMER table, I
have:

CustomerID
Name
Phone
etc...

In my CALLS table I have:

CustomerID
CallType
CallTime
etc....

I'll have multiple calls to one CustomerID. I want staff members to pull
a query of qualified customers. The qualifier would be no "Not
Interested" CallTypes. So if the customer had 3 calls and one of them
happened to be "Not Interested" in the CallType, I want to hide that
CustomerID altogether and not see any of the 3 calls.

Does this make sense? Help appreciated.
Frank


*** Sent via Developersdex http://www.developersdex.com ***


http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

had 3 calls and one of them happened to be "Not Interested" in the
CallType, I want to hide that CustomerID altogether and not see any of
the 3 calls. <<

Here is one way of doing this:

SELECT customer_id
FROM Calls
GROUP BY customer_id
HAVING SUM(CASE WHEN call_type = 'Not Interested' THEN 1 ELSE 0 END) =
0;

Or

SELECT C1.*
FROM Customers AS C1
WHERE NOT EXISTS
(SELECT *
FROM Calls AS C2
WHERE C1.customer_id = C2.customer_id
AND call_type = 'Not Interested' );

Sorry for not posting DDL before, but this last example worked great.
Thanks very much.

Frank


*** Sent via Developersdex http://www.developersdex.com ***