>I used a JOIN-statement to make this work,
> however it has the odd-behaviour that it only
> shows the records which any of the users has
> already accessed. So there has to be an entry
> for a master record in the child table for the
> user (which has never accessed the record) to
> see the record.

I believe you have the tables inverted in your SQL statement. You might need to either use a RIGHT OUTER JOIN or invert tables in your LEFT OUTER JOIN (e.g. SELECT * FROM master LEFT OUTER JOIN tableuser).


>Hey there,
>
>I'm building some dynamic SQL in a formclass which at run-time fills in some values based on a list of values:
>
> 1. Active user's login-code
> 2. Combo-box which determines the 'where' clause
> 3. Combo-box which determines the 'order by' clause
> 4. Class-property with an additional 'where' clause
>
>The construction is that there's a master-table with some description records, and a child-table matching these records to the users of the system. The child table contains information on whether or not the user wants to see this record, if it's a favorite record and the last time the user accessed this record.
>
>The structure is like this:
>
>tbl_master
>----------
>ID N 6
>DESC C 50
>INSTALLED L
>
>tbl_child
>---------
>ID N 6
>USER C 5
>NOTSHOWN L
>FAVORITE L
>ACCESSED T
>
>I used a JOIN-statement to make this work, however it has the odd-behaviour that it only shows the records which any of the users has already accessed. So there has to be an entry for a master record in the child table for the user (which has never accessed the record) to see the record. The statement is like this:
>
>

>
>* Here the basics of the SQL statement are created, including the join to the usertable
>
>lcSelect = "SELECT "+ lcTblMaster + ".id, desc, notshown FROM " +lcTblUser
>lcSelect = lcSelect + " LEFT OUTER JOIN " + lcTblUser + " ON " + lcTblMaster + ".id = " + lcTblUser + ".id AND "
>lcSelect = lcSelect + lcTblUser + ".user = '" + ALLTRIM(oApp.cUserName) + "'"
>lcSelect = lcSelect + " WHERE installed == .T. AND "
>
>* A case structure is used to decide which specifics are needed for the current view, in a general way
>
>DO CASE
>	CASE This.cmbRomView.Value = "Default" && All 'shown' records
>		lcSelect = lcSelect + " NOT notshown == .t. "
>	CASE This.cmbRomView.Value = "Favorites"
>		lcSelect = lcSelect + " favorite == .t. "
>	CASE This.cmbRomView.Value = "Alles"
>    	           lcSelect = lcSelect + " 0=0 "
>	OTHERWISE
>    	           lcSelect = lcSelect + " 0=0 "		
>ENDCASE
>
>* Using a property on the form, specific filtering options can be set
>
>IF LEN(This.csqlwhere) > 0
>	lcSelect = lcSelect + " AND " + This.csqlwhere
>ENDIF
>
>* Set the displaying order based on the second combo
>
>lcSelect = lcSelect + ' order by '
>
>* For future expansion, this has already been changed into a CASE. Right now it seems a bit overkill
>
>DO CASE
>	CASE This.cmbSorteren.Value = 'desc'
>		lcSelect = lcSelect + ' desc '
>	CASE This.cmbSorteren.Value = 'lastaccess'
>		lcSelect = lcSelect + ' accessed desc '
>	OTHERWISE
>		lcSelect = lcSelect + ' desc '
>ENDCASE
>
>* Specify the destination of the result
>
>lcSelect = lcSelect + " Into Cursor crsViewTemp "
>
>* Take the command, and place it on the listbox
>
>ThisForm.lstView.RowSource = lcSelect
>
>

>
>Changing the join-statements doesn't work. I tried creating the same thing with the view-designer, but it somehow keeps messing up. Does anyone have an idea what's wrong with this?