Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Some help with a sub-query
Message
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
00637702
Message ID:
00638303
Views:
19
Caroline,
>>>Would you know if there is a way to force the group by to return say records of type 1 if the pre Group By result holds both a type 1 and a type 0?
>If you ran your select without the Group By your result would contain some Keys with both a type 1 and a type 0. I would like to force the Group By to select the records of a particular type first so that the result would contain Type 1's if both 1's and o's where present.

Hmmm ... you could try this. You'll get all records from exhibit of type 1, that have at least one record in Links of type 0 and of type 1.
Select Exhibit.iid, Exhibit.nkey, Links.* ;

  FROM Exhibit ;

  LEFT JOIN Links ;
  ON Exhibit.iid = Links.iwork ;

  WHERE Links.itype = 1 ;
    AND EXISTS (SELECT 1 ;
                  FROM Links l1 ;
                  WHERE l1.itype = 0) ;
    AND EXISTS (SELECT 1 ;
                  FROM Links l2 ;
                  WHERE l2.itype = 1) ;

  GROUP BY Exhibit.iid, links.iwork ;

  INTO CURSOR Linked
HTH,
Armin

Armin Neudert
Regional Director (Stuttgart) of German FoxPro User Group dFPUG

MCP for Visual FoxPro

Expert/Editor of the VFP section in the German Codezone community portal
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform