My Join in Sql is not working

[Marc Venken]

Hello,

Normaly this should be easy, but I didn't find it yet... I have looked at samples Maria02 and 05 but still ...

Here is the code

Code: Select all  Expand view  RUN

function Main()

  local oRs, cSql

  Public cServer     := ""
  Public cDataBase   := ""
  Public cUser       := ""
  Public cPassWord   := ""
  Public oCn, oRsATT, oRsVolgorde

  FWCONNECT oCn HOST cServer USER cUser PASSWORD cPassword DATABASE cDatabase

  if oCn == nil
     ? "Failed to connect"
     return nil
  endif

  cZoekProduct := "77"

  oRsProduct := oCn:RowSet( "SELECT * FROM ps_product_lang where id_lang = ? ", { "1" } )

  oRsGroepen:= oCn:RowSet( "SELECT * FROM `ps_category_lang` where `id_lang` = ? ", { "1" } )

  oRsvolgorde:= oCn:RowSet( "SELECT * FROM `ps_category_product` where `id_category` = ? ", { cZoekProduct } )

//  oRsvolgorde:= oCn:RowSet( "SELECT * FROM, P.description_short as PName from ps_category_product C LEFT JOIN ps_product_lang P on C.id_product where id_category = ? " )


  DEFINE FONT oBold NAME 'CALIBRI' SIZE 0,-12 BOLD
  DEFINE FONT oFont NAME "CALIBRI" SIZE 0,-12
  DEFINE FONT oFontS NAME "Segoe UI" SIZE 0,-09

  DEFINE DIALOG oDlg SIZE 1410,800 PIXEL TRUEPIXEL ;
  STYLE nOR( DS_MODALFRAME, WS_POPUP, WS_CAPTION, WS_SYSMENU,WS_MAXIMIZEBOX, WS_MINIMIZEBOX, WS_THICKFRAME );
  TITLE "Ploegen";
  GRADIENT { { 1, nRGB( 125, 155, 175 ), nRGB( 125, 155, 175 ) } }

/////////////////////////////////////////////////////////////////  /////////////////////////////////////////////////
   aVelden1 :=  { ;
   { "id_category"    , "Cat_ID"          ,nil,  50 }, ; // 1
   { "name"           , "Naam"            ,nil,  200 }, ; // 2
   { "position"       , "Pos"             ,nil,  50 }}   // 9

   @ 1,1 XBROWSE oBrw1 size 1100,300 PIXEL OF  oDlg font oFont ;
      DATASOURCE oRsgroepen;
      COLUMNS aVelden1;
      AUTOSORT CELL LINES NOBORDER FOOTERS
      //FASTEDIT

     oBrw1:nEditTypes = EDIT_GET
     oBrw1:SetChecks()


     oBrw1:lF2KeyToEdit := .t.  // Edit when F2 is pressed
     oBrw1:nHeadStrAligns  := AL_CENTER

   WITH OBJECT oBrw1

      :lColChangeNotify := .t.

      :oHeaderFonts     := oBold

      :bClrEdits        := { || { CLR_BLACK, CLR_YELLOW }}
      :bClrRowFocus     := { || { CLR_BLACK, RGB(185,220,255) } }

      :nColDividerStyle := LINESTYLE_LIGHTGRAY
      :nRowDividerStyle := LINESTYLE_LIGHTGRAY
      :bClrRowFocus     := { || { CLR_BLACK, RGB(185,220,255) } }
      :nMarqueeStyle    := MARQSTYLE_HIGHLROWMS

      :lFooter          := .t.
      :bRecSelHeader    := { || "RowNo" }
      :bRecSelData      := { |o| o:KeyNo }
      :bRecSelFooter    := { |o| o:nLen }
      :oRecSelFont      := oFont  // optional
      :nRecSelWidth     := "99999" // required size

      :bOnChange     := { || oRSNewGroep(oBrw1:cat_id:Value), oBrw:refresh() }

   END

   oBrw1:CreateFromCode()


/////////////////////////////////////////////////////////////////  BRW FOLDER 3 /////////////////////////////////////////////////

   aVelden3 :=  { ;
   { "id_category"    , "Cat_ID"          ,nil,  50 }, ; // 1
   { "id_product"     , "Product_ID"      ,nil,  70 }, ; // 2
   { "position"       , "Pos"             ,nil,  50 }, ;   // 9
   { "oRsProduct:description_short"       , "Naam"             ,nil,  150 }}   // 9
*/

   @ 400,1 XBROWSE oBrw size 1100,300 PIXEL OF oDlg font oFont ;
      DATASOURCE oRsvolgorde;
      COLUMNS aVelden3;
      AUTOSORT CELL LINES NOBORDER FOOTERS

     oBrw:nEditTypes = EDIT_GET
     oBrw:SetChecks()


     oBrw:lF2KeyToEdit := .t.  // Edit when F2 is pressed
     oBrw:nHeadStrAligns  := AL_CENTER

   WITH OBJECT oBrw

      :lColChangeNotify := .t.

      :oHeaderFonts     := oBold

      :bClrEdits        := { || { CLR_BLACK, CLR_YELLOW }}
      :bClrRowFocus     := { || { CLR_BLACK, RGB(185,220,255) } }

      :nColDividerStyle := LINESTYLE_LIGHTGRAY
      :nRowDividerStyle := LINESTYLE_LIGHTGRAY
      :bClrRowFocus     := { || { CLR_BLACK, RGB(185,220,255) } }
      :nMarqueeStyle    := MARQSTYLE_HIGHLROWMS



      :lFooter          := .t.
      :bRecSelHeader    := { || "RowNo" }
      :bRecSelData      := { |o| o:KeyNo }
      :bRecSelFooter    := { |o| o:nLen }
      :oRecSelFont      := oFont  // optional
      :nRecSelWidth     := "99999" // required size

   END

   oBrw:CreateFromCode()

   ACTIVATE DIALOG oDlg CENTERED

   RELEASE FONT oFont
   oCn:close()

return nil

function OrsNewPos(cData)
  cZoek = alltrim(cData)
  oRsATT:Requery( { cZoek } )
  cZoekID = oRsAtt:id_attribute
  oRs:Requery( { cZoekID } )
return NIL

function oRsNewGroep(cData)
  cZoek = alltrim(str(cData))
  oRsvolgorde:Requery( { cZoek } )
  oRsProduct:Requery( { cZoek } )

  //cZoekID = oRsAtt:id_attribute
  //oRs:Requery( { cZoekID } )
return NIL

 

In the second browse, the name is not changed, because It need to come from ps_products_lang

[AntoninoP]

I am not sure about your problem, the only join i see is :

Code: Select all  Expand view  RUN

SELECT * FROM, P.description_short AS PName FROM ps_category_product C LEFT JOIN ps_product_lang P ON C.id_product WHERE id_category = ?

and here I think there it was a "paste" in the wrong place, maybe you want write:

Code: Select all  Expand view  RUN

SELECT *,P.description_short AS PName FROM ps_category_product C LEFT JOIN ps_product_lang P ON C.id_product WHERE id_category = ?

PS. I see 12:37 am maybe you need to sleep

[Armando]

Mar:

Code: Select all  Expand view  RUN

oRsvolgorde:= oCn:RowSet( "SELECT * FROM, P.description_short as PName from ps_category_product C LEFT JOIN ps_product_lang P on C.id_product where id_category = ? " )
 

I can see the other side in ON clause

Code: Select all  Expand view  RUN

 Rsvolgorde:= oCn:RowSet( "SELECT * FROM, P.description_short as PName from ps_category_product C LEFT JOIN ps_product_lang P on C.id_product = ??????  where id_category = ? " )
 

Pls, look at the blue questions mark

Regards

[AntoninoP]

Hello,
There is still a comma after the FROM that I am not sure is valid. But the query

Code: Select all  Expand view  RUN

SELECT * FROM Table1,Table2

is valid, then maybe the comma does not influence your query...

I think the correct query for you is:

Code: Select all  Expand view  RUN

SELECT P.description_short AS PName FROM ps_category_product C LEFT JOIN ps_product_lang P ON C.id_product = P.id_product WHERE id_category = ?

or is

Code: Select all  Expand view  RUN

SELECT C.*,P.description_short AS PName FROM ps_category_product C LEFT JOIN ps_product_lang P ON C.id_product = P.id_product WHERE id_category = ?

[Marc Venken]

The sample Maria02 and 05 works and is showing 2 tables linked togetter, so that the xbrowse is showing 2 table informations

In the sample Xbrowser is used as function, not as command Xbrowse.

Do you have a sample where xbrowse is used not as function ?