Help with SQL - hard to describe in a few words....

[ProNetGroup]

Please Help!

I am running the latest version of Microsoft Visual FoxPro, and am having difficulties creating a SQL statement. I am trying to create a report that lists all of our contacts in the database, and includes the last call to that person, if one exists. Basically, I have a table of contact information, client information (company/address info), and calls. (Calls is a table that contains a log of who called this person and what they talked about, etc.) So right now, this is what I have:

SELECT calls.contactid, calls.isclient, calls.initials, calls.date, calls.memo, calls.clientid, clients.clientid, clients.address, clients.address2, clients.city, clients.state, clients.zip, contacts.id, contacts.lastname, contacts.firstname, contacts.jobtitle, contacts.workphone, contacts.workext, contacts.work2, contacts.cellphone, contacts.cell2, contacts.faxnumber, contacts.email, contacts.branch, contacts.division FROM calls, clients, contacts WHERE contacts.id = calls.contactid AND calls.clientid = clients.clientid GROUP BY calls.contact

This SQL works just fine, but the only thing is that the only contacts that I grab are the ones that actually have a call entered, when what we want is all contacts in the database, showing the last call entered if they do have calls. It occurred to me that because some contacts don't have calls, that it might produce some kind of error and not be possible at all, but I am just not sure. Does anyone have any suggestions for me???

Thanks so much for your help!!!
Eubanks

 

[Mike Gagnon]

This may not solve it but,
If you are using VFP8.0 ("I am running the latest version of Microsoft Visual FoxPro&quot, the field in the group by clause should be added to the list in your SQL.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[ProNetGroup]

This is very true about my GROUP BY field - what I was doing before was pulling the contact name straight from the calls table, which will certainly only pull contacts that already have calls. So what I did was grab all of the contact information, including name, from the contacts file (where there is a list of all contacts), however I am still having the problem of only pulling up contacts that have calls, which makes sense because I include the WHERE contacts.id = calls.contactid AND calls.clientid = clients.clientid in the SQL statement. But, is there a way to grab these records, and then also grab all the other contacts out of the contacts file that DO NOT have calls and put them into one report, sorted by lastname?

Any other ideas?

Thanks for the help, and any more help would be greatly appreciated!!!
Eubanks

 

[mkrausnick]

You need an OUTER JOIN to be able to retrieve contacts with no calls. If you also have contacts with no client records, you would also need an OUTER JOIN for that relationship too. I haven't done this in awhile, but I seem to remember that you can't OUTER JOIN two tables in the same SQL select or if you can, the table with the most records (CONTACT in this case) has to be first so the joins are LEFT OUTER rather than RIGHT OUTER. Again, that used to apply but it may not be the case now.

You will have to play with the order of the tables, but I'd start by putting CONTACT first, so you would use a LEFT OUTER join, like this:

SELECT ... FROM contacts LEFT OUTER JOIN calls on contacts.id = calls.contactid LEFT JOIN CLIENTS ON contacts.id = clients.clientid

Note the joining of all tables to contacts rather than cascading contacts to calls and calls to clients.

Hope this gives you something to work with.






Mike Krausnick

 

[mkrausnick]

Also, unless I am missing something, since you don't have any aggregate functions (sum, count, etc) in your select, I question the use of the GROUP BY. If the purpose of the GROUP BY is to get unduplicated results, you can use SELECT DISTINCT to accomplish that.



Mike Krausnick

 

[ProNetGroup]

The purpose of using the GROUP BY was to only get the last call entered...I did a GROUP BY on the contact's name because otherwise it would (originally at least) act like the same person, for instance, Bob Smith, was actually two different people and list it twice with two different calls. So, when I added the GROUP BY, it eliminated duplicate records and seemed to include only the latest call. Probably not the most efficient way...I am not too experienced in SQL to begin with and haven't used it in a while, so any suggestions are welcomed!

Also, what you are saying about the OUTER JOIN sounds familiar to me, but again, I haven't worked with JOINS in a while myself. This is what I have come up with, and it is not working:

SELECT calls.contactid, calls.isclient, calls.initials, calls.date, calls.memo, calls.clientid, clients.clientid, clients.address, clients.address2, clients.city, clients.state, clients.zip, contacts.id, contacts.lastname, contacts.firstname, contacts.jobtitle, contacts.workphone, contacts.workext, contacts.work2, contacts.cellphone, contacts.cell2, contacts.faxnumber, contacts.email, contacts.branch, contacts.division FROM contacts LEFT OUTER JOIN calls on contacts.id = calls.contactid, clients

As far as the clients/contacts go, we have a complicated relationship between them...(first of all, clients really contains company name/office information, and clients contains information about the contact, like name, telephone numbers, etc.)...because it is a little bit complicated, we have set up a table called xref that cross-references the current clientid with the current contactid. Therefore, I did not try to do the LEFT OUTER JOIN on the clients table also, might I need to use that xref table??? The xref table contains these fields: clientid, contactid, and current. The clientid and contactid fields are obviously unique numbers that identify which client and which contact. The current field is a boolean T/F field. The unique combination of the three says which contact is at which office right now. Any more suggestions?

Sorry this is all confusing!!! But thanks for the help so far!

Eubanks

 

[mkrausnick]

Well, this certainly is a challange!
First, what about creating a view of contacts and clients, using the xref table, and then joining the view to the calls table? We had a similar app (call tracking, multi-level contacts/client addresses) some years ago and it seems like that's how we did it.

Second in a true RDBMS, you won't necessarily get the latest call using the technique you describe. To do it right, you should extract all the latest calls (by CLIENT ID) into a cursor, view, or temp table, and then join the contacts to that. There was a fairly long thread last week on how to do this, but essentially, you use max() on the call date or other field and then a second select using
&quot;WHERE <field> IN&quot; a sub-select of the cursor.

And this seems like as good a place as any to ask a really dumb question: How do I place the reference to a thread or FAQ in my response? I've never been able to figure out how to do that.










Mike Krausnick

 

[craigsboyd]

Mike Krausnick,

faq184-3536

thread184-541862

...Just type those in your post and you will see them come up as links. Use Preview Post to make sure you are getting it right. Now the thread we are in now has SQID=541862 and SPID=184 up in the browser address bar. This forum is 184 and SQID is the thread number. With FAQs it is the same except there is a SFID=3536 and SPID=184 in the address bar SFID is the FAQ number. Easiest way: is to look at the top left of the thread or FAQ you are in (up by the title) and you will see the links like in my post here, just copy-n-paste into your post.

Slighthaze = NULL

 

[mkrausnick]

OK, that works. My error was I wasn't previewing to see the result. Thanks,

Mike Krausnick

 

[ProNetGroup]

Okay, I have been reading over the last post about creating a view of contacts and clients using the xref table and also using a cursor to extract the latest call and temporarily store it to use it in a report. The concepts presented make sense to me, but I was wondering if anyone had any ideas of a starting point to do this. I am a very inexperienced &quot;programmer&quot;, if you even want to call me that at all, and could use any help that is offered! Also, I have not tried to look up the other thread about extracting the latest call and placing it in a cursor, so I still need to do that.

Thanks a bunch!
Eubanks

 

[mkrausnick]

The caller view (the customer who initiated the call ticket):
create view caller as select
contacts.id, contacts.lastname, contacts.firstname, contacts.jobtitle, contacts.workphone, contacts.workext, contacts.work2, contacts.cellphone, contacts.cell2, contacts.faxnumber, contacts.email, contacts.branch, contacts.division,
clients.clientid, clients.address, clients.address2, clients.city, clients.state, clients.zip
from contacts, clients, xref where
contacts.id=xref.contactid and clients.clientid=xref.clientid and
xref.current = .T.

So ignoring orphan contacts and companies with no contacts, Now you have a logical one-record-per-person &quot;caller&quot; view.

To create a cursor (you could also use a temp table, but you can't use a view for this) of just the latest calls for each contact:

select contactid, max(date) from calls group by contactid into cursor c1

To get the caller info combined with the latest call info:


select distinct calls.*,caller.* from c1,calls,caller where
c1.contactid=calls.contactid and c1.date=calls.date and calls.contactid = caller.contactid

Notes:

The &quot;DISTINCT&quot; clause gets rid of duplicates where a caller placed more than one call in a day. It could be eliminated if CALLS.DATE is actually a DATETIME type.

You probably can't use a view for C1 because it would be based on CALLS, which you're going to turn around and use in the second select.

You might have to play with these two SQL statements to get what you want but I think this approach should work. No warranty though.

I did not take execution speed into account. This might be slow, but if it works you can then tune it for speed.

I wonder if tuning your database design slightly could simplify this at all. It seems to me if reporting the latest call is important to your model, maybe storing the callid of the latest call somewhere (either in CONTACTS or XREF) might be worthwhile.

Anyway, let me know what problems you have with this, and we'll tweak it if necessary.

BTW the other thread I referred to is thread183-538296. It's not exactly what you're doing, but you might be able to apply something from there to your problem.





Mike Krausnick

 

[ProNetGroup]

Thanks for the advice...I have been trying to work with it but without too much luck.

Do I just write these three separate select statements one after the other? I have never written more than one select statement in a SQL statement before...I am merely a rookie.

Also, I am getting this error: &quot;No database is open or set as the current database&quot;. Any ideas?

Thanks so much for all the help so far!!!
Eubanks

 

[mkrausnick]

You enter the first CREATE VIEW command only once. But before creating a view, you have to tell VFP where to store it. Type:
OPEN DATABASE <databasename> SHARED
in the VFP command window, then type the CREATE VIEW command:

create view caller as select
contacts.id, contacts.lastname, contacts.firstname, contacts.jobtitle, contacts.workphone, contacts.workext, contacts.work2, contacts.cellphone, contacts.cell2, contacts.faxnumber, contacts.email, contacts.branch, contacts.division,
clients.clientid, clients.address, clients.address2, clients.city, clients.state, clients.zip
from contacts, clients, xref where
contacts.id=xref.contactid and clients.clientid=xref.clientid and
xref.current = .T.
in the command window.

Opening the database first should eliminate the error message.

Next, in the command window, just for testing, type the other two selects:

select contactid, max(date) from calls group by contactid into cursor c1

followed by:

select distinct calls.*,caller.* from c1,calls,caller where
c1.contactid=calls.contactid and c1.date=calls.date and calls.contactid = caller.contactid

Hopefully, this displays a browse window that contains the data on which you want to report.

Now, a word of warning: I didn't actually tried this SQL to see if it would work. Some tweaking may be required, and it is possible that using a view in this way will confuse VFP. But give it a try.

If it works, you would need to either create a form or a program (PRG file) to run the two SQL select statements to create a cursor for the report to use as it's data source.

I probably forgot one or two things, so if it doesn't work, post back.

Lastly, I will be offline for one week starting on May 16th, so I won't be able to answer your posts from May 16th until about May 26th. But there are many other helpful experts here to guide you.

Also, for straight SQL assistance, the MS SQL SERVER-PROGRAMMING forum is another a good resource.








Mike Krausnick

 

[ProNetGroup]

Okay, first of all I will admit that I never ended up trying your way in the command box in FoxPro like you had suggested. I was actually trying your code in a program that was written by someone other than myself. Basically, I just type in the SQL, and can hit different buttons to execute different functions, i.e. show the results in a browse or show the results on the report that I created. With that in mind, I did try to use the LEFT JOIN function within the SQL, and finally got it to work without errors, however the same results come up, where the contacts without calls still do not show up. I need all contacts to show up whether or not they have a call, and display the call if one was made. (And yes, you have certainly brought up another issue of bringing up the most recent call by using the MAX function and storing it elsewhere, but I am going to take it one step at a time and see if I can pull all contacts first, so we shall see...) So, here is the SQL that I am executing:

SELECT calls.contactid, calls.isclient, calls.initials, calls.date, calls.memo, calls.clientid, clients.clientid, clients.company, clients.address, clients.address2, clients.city, clients.state, clients.zip, contacts.id, contacts.lastname, contacts.firstname, contacts.jobtitle, contacts.workphone, contacts.workext, contacts.work2, contacts.cellphone, contacts.cell2, contacts.faxnumber, contacts.email, contacts.branch, contacts.division FROM clients, contacts left join calls on calls.contactid = contacts.id WHERE calls.clientid = clients.clientid ORDER BY contacts.lastname, contacts.firstname

What is wrong with my syntax where it will not pull all records from the contacts table and the matching records from the calls table?

Any help is appreciated!!! And Mike, thanks for all of the previous help!

Eubanks

 

[mkrausnick]

&quot;LEFT JOIN&quot; should read &quot;LEFT OUTER JOIN&quot;. The &quot;OUTER&quot; is what causes contacts with no calls to be listed.

Mike Krausnick

 

[mkrausnick]

Also, change the &quot;CALLS.CLIENTID=CLIENTS.CLIENTID to refer to CONTACTS instead of CALLS, because you always have a contact but you do not always have a call.
i.e. &quot;CONTACTS.CLIENTID=CLIENTS.CLIENTID&quot;

Mike Krausnick

 

[ProNetGroup]

Sweet - that last message about connecing the clientid from contacts rather than calls appears to have solved part of my problem. Yippie!!! Hopefully it solved lots, but I am going to look into it in more detail tomorrow morning.

Thanks so much!!!
Eubanks