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

I don't see any logic gaps in your WHERE statement (as far as your table links are concerned), so I think something else is going on. I usually troubleshoot these things by simplifying the SELECT to the bare minimum (start with just CLINK, issues and calllist) without any conditionals (the BITAND and SelRep stuff), look at the resultant records, and then add one step at a time from there. If you just think about how you expect your next addition to affect the records and fields you see in from of your face, you'll probably see the problem.