Mysql NESTED Join

Applications Grid / Reports
, ,
#1

Tried to make a NESTED Join within the SQL of a GRID APPLICATION. The query works fine in the SQL Builder in scriptcase and with SqlPro (desktop application). Here is the query.

SELECT a.iniid,
   a.cuiid,
   a.cuiid,
   c.wosLetter,
   a.insDescription,
   a.sriid,
   a.indDateOfOrder,
   a.indDateOrderDue,
   a.inbRushOrder
FROM invoice a LEFT OUTER JOIN (workorder c INNER JOIN workflow d ON c.woiid = d.woiid)
ON a.iniid = c.iniid
WHERE d.wfiManagement = '2' OR d.wfiManagement = '1' AND a.loiid = 2

Compile and Run application:
Fatal error: Call to a member function MoveNext() on a non-object in /usr/local/zend/apache2/htdocs/scriptcase6/devel/lib/php/database.inc.php on line 882

Sometimes lets me compile the application but when it loads and runs:
Error while accessing the database:
Column ‘iniid’ in field list is ambiguous
SelectLimit(SELECT iniid, cuiid, cuiid as cuiid_1, insDescription, sriid, indDateOfOrder, indDateOrderDue, inbRushOrder from invoice a LEFT OUTER JOIN (workorder c INNER JOIN workflow d ON c.woiid = d.woiid) ON a.iniid = c.iniid where (d.wfiManagement = ‘2’) OR (d.wfiManagement = ‘1’) AND (a.loiid = 2), 22, 0)

Thoughts as to why it will work in the SQL builder (scriptcase tool) and within my SQLPRO application. I would believe this to be in error within SCRIPTCASE not being able to use a NESTED SQL.
Kris

#2

Turn on debug mode in the application. Often the sql query you write becomes nested inside another query that scriptcase creates. Turning on debug mode allows you to see the full query and debug it. I’ve used nested queries and they work well in grids.

#3

Yes I too have had trouble like this and it has to do with things SC generates. These functions are made for single table use and I useually create views to achieve things like this.

#4

Hello,

As rperrett said, turn on debug mode and use alias if necessary.

regards,
Bernhard Bernsmann

#5

+1 for views. I’ve had to implement them extensively.

#6

I created a view using SQL Query Builder in SC and get similar Error!

SELECT
Diseases.DiseaseID,
Diseases.ComonName,
Diseases.ComonSubname,
Diseases.MedicalName,
Diseases.LatinName,
Diseases.OtherName,
Recipe Component.DiseaseID,
Recipe Component.RecepieID,
Recipe Component.ComponentCode,
Recipe Component.ComponentDescription,
Recipe Component.Unit,
Recipe Component.Amount,
Recipe Ingredients.DiseaseID,
Recipe Ingredients.RecepieID,
Recipe Ingredients.ProductId,
Recipe Ingredients.IngredientName,
Recipe Ingredients.IngredientDescription,
Recipe Ingredients.Unit,
Recipe Ingredients.Amount
FROM
dDISEASES Diseases INNER JOIN dRECCOMPONENTS Recipe Component ON Diseases.DiseaseID = Recipe Component.DiseaseID
INNER JOIN dRECINGREDIENTS Recipe Ingredients ON Diseases.DiseaseID = Recipe Ingredients.DiseaseID

ERROR:

Fatal error: Call to a member function FieldCount() on a non-object in C:\Program Files\NetMake\v6\wwwroot\scriptcase\devel\lib\php\database.inc.php on line 855

so how AM I supposed to implement a VIEW ?

Arthur

#7

Look at the comma after Diseases.OtherName …

#8

thanks but,…it is not a comma…

with or without the comma I get the same error.
It seems like SC just expects certain number of fields in a QUEUE and SQL query deals with much more. Perhaps I’m worng but I still see nor resolution.
I have seen some other users ask similar questions with using more advanced queries and I wonder if I placed the query in the right place (perhaps views could be only used in source code) ???

BTW: this query was build with SC query builder

Arthur

SC6_SQLError.jpg

#9

Can you try this query?


SELECT
Diseases.DiseaseID,
Diseases.ComonName,
Diseases.ComonSubname,
Diseases.MedicalName,
Diseases.LatinName,
Diseases.OtherName,
RC.DiseaseID,
RC.RecepieID,
RC.ComponentCode,
RC.ComponentDescription,
RC.Unit,
RC.Amount,
RI.DiseaseID,
RI.RecepieID,
RI.ProductId,
RI.IngredientName,
RI.IngredientDescription,
RI.Unit,
RI.Amount
FROM dDISEASES AS Diseases 
INNER JOIN dRECCOMPONENTS AS RC ON Diseases.DiseaseID = RC.DiseaseID
INNER JOIN dRECINGREDIENTS AS RI ON Diseases.DiseaseID = RI.DiseaseID
#10

Reinhard - thank you for your time…
this query looks little strange to me but it doesn’t generate an error now. Although when I run it and open the grid it shows no records, so probably something is wrong with joints.

can you please explain how come you used "RI’ instead “dRECINGREDIENT” as a real table name ?

Arthur

#11

INNER JOIN dRECINGREDIENTS AS RI => you can use RI.<fieldname>

Ok, what say the debug output (SQL String) when you start the app?

#12

in sc tool inner join not working

SELECT * from exp_familylevelintervention AS ab INNER JOIN family AS fc ON ab.fli_FmlId=fc.fml_Id order by fc.fml_Firstname

after run it show error “Search fields undefined”

please reply