>>Hello, I have encountered another problem with my application. I have had to modify the base table structure to allow for a many-to-many relationship where I originally had it setup as only a one-to-many.
>>
>>I had an SQL SELECT statement that would pull the data from the associated tables I needed and it was working flawlessly, that is, until I changed the table structures.
>>
>>Now I cannot seem to get the SELECT statement to locate the correct number of records. It either picks none at all, or multiple instances of both records from the two tables.
>>
>>There are 3 tables involved, 2 have a single primary key (these are the base tables) and another single table which stores the associations between the first two tables. This table has a single primary key and two regular keys that link to the primary keys of the first two tables.
>>
>>Do I need to use a nested select statement? I have been using filters to define my table relationships, but would it be easier to use the JOIN clause?
>>
>>Any help would be appreciated. Thanks in advance...
>>
>>Iain
>
>can we see the select statement you're using?

This isn't very pretty (it's my first SELECT statement...) but here it is.

SELECT ALLTRIM(Reps.rep_usrnme) AS for,;
Customers.cust_name AS customer, Customers.cust_rank AS rank,;
Calllist.call_contact AS caller, Calllist.call_number AS phone,;
Calllist.call_end AS logged, Issues.issue_flags AS flags,;
Issues.issue_description, Issues.issue_subj, Calllist.call_key,;
Issues.issue_key, DATETIME()-Calllist.call_end AS inqueue,;
BITAND(Issues.issue_flags,2)=2 AS priority, Cilink.ci_key;
FROM calls!reps, calls!customers, calls!calllist, calls!issues,;
calls!cilink;
WHERE Issues.issue_key = Cilink.issue_key;
AND Reps.rep_key = Issues.rep_for;
AND Calllist.call_key = Cilink.call_key;
AND Customers.cust_key = Calllist.cust_key;
AND (BITAND(Issues.issue_flags,4) = 4;
AND Issues.rep_for = ?cSelRep);
ORDER BY 13 DESC, 12 DESC, Customers.cust_rank DESC

The CILINK table is my link table between the CALLLIST table and the ISSUES table. It has the two 'child' links. The CALLLIST & ISSUES table only have unique primary keys to identify them in their tables, there are no links back to the CILINK table.

This SQL statement pulls in only 2 records, even though I have 4 records in ISSUES where (BITAND(Issues.issue_flags,4)=4) = .T. It is pulling in only the records that have multiple instances of a single CALLLIST record in the CILINK table. All other records in the CILINK table do not get pulled in. I want to make it so that every ISSUES record that has this bit set, gets pulled in along with the associated CALLLIST information.

This SQL statement is a hack and is quite messy at the moment. I'm not really sure where I should trim, and what order some of the clauses need to be in to make it work the way I would like.

Please let me know if you can help me out. Thanks for your time.