Outer Join question

Results 1 to 3 of 3

Thread: Outer Join question

  1. #1
    Mitch Trope Guest

    Default Outer Join question

    I am creating a field-dynamic site that will display a different form to fill out based on the entity the user is coming from. The database is constructed as follows:<BR>ENTITY - contains entity id field, name of entity<BR>ENTITY_FIELD - contains field ids for all fields in all forms, field names and type id&#039;s<BR>ENTITY_RELATION - relates entity.entity_id and entity_field.field_id, whether a field is required or not and the sortby for each field<BR>FIELD - contains field type id&#039;s and descriptions (i.e. text, integer, drop down box, etc.)<BR>UNIVERSAL_DATA - contians entity_id, field_id, client_id (all 3 form a combined key) and data from the form in another field<BR><BR>The problem I&#039;m having is populating the form with existing data. It displays empty, and can repopulate if you hit "back" on your browser, but I need to outer join the data field onto an inner join query that has the rest of the information I need to display the form properly. Here is what I tried:<BR><BR>SELECT r.ENTITY_ID, n.ENTITY_DESC, r.Required, e.FIELD_NAME, r.FIELD_ID, e.TYPE_ID <BR>FROM ENTITY n, ENTITY_RELATION r, ENTITY_FIELD e, FIELD f <BR>where r.ENTITY_ID=2 and n.ENTITY_ID=2 and r.field_id=e.field_id and e.TYPE_ID=f.TYPE_ID <BR>ORDER BY SORTBY <BR>left outer join (<BR> select ud.data, ud.entity_id, ud.field_id <BR> from universal_data ud, entity n, entity_relation r, entity_field e, field f<BR> where ud.fedid=888888888 and ud.entity_id=2) oj<BR>on r.field_id = oj.field_id and<BR> r.Entity_id = oj.Entity_id<BR><BR>Query Analyzer gives me this error message:<BR>Server: Msg 156, Level 15, State 1, Line 5<BR>Incorrect syntax near the keyword &#039;left&#039;.<BR>Server: Msg 170, Level 15, State 1, Line 8<BR>Line 8: Incorrect syntax near &#039;oj&#039;.<BR><BR>Any help would be greatly appreciated. Thanks in advance.<BR><BR>Mitch Trope

  2. #2
    Join Date
    Dec 1969

    Default RE: Outer Join question

    1. You have to place the ORDER BY statement below all the join statements<BR><BR>2. What is oj? if you try to do an outer join, you have to write it out (OUTER JOIN)<BR><BR>Peter

  3. #3
    Mitch Trope Guest

    Default Got it!

    I figured it out - here is what I came up with if you&#039;re interested:<BR><BR>SELECT EntityID,Fieldid,entity_desc,required,field_name,t ype_id,oj.data<BR>FROM<BR>(select r.ENTITY_ID as Entityid, n.ENTITY_DESC as Entity_desc, r.Required as required, e.FIELD_NAME as field_name, r.FIELD_ID as fieldid, e.TYPE_ID as type_id<BR>FROM ENTITY n, ENTITY_RELATION r, ENTITY_FIELD e, FIELD f <BR>where r.ENTITY_ID=2 and n.ENTITY_ID=2 and r.field_id=e.field_id and e.TYPE_ID=f.TYPE_ID and r.entity_id = n.entity_id) H <BR>left outer join <BR> (select ud.data,ud.entity_id,ud.field_id<BR> from universal_data ud<BR> where ud.fedid=888888888 and ud.entity_id=2) oj<BR>on h.Entityid = oj.entity_id <BR>and h.fieldid = oj.field_id

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts