Inner join on MYSQL

vallemanden · Post by **vallemanden** » Tue 02 Aug 2011 12:18

Hi

itemQuery.Close;
ItemQuery.SQL.Clear;
Itemquery.sql.BeginUpdate;
Itemquery.SQL.Add('Select * FROM mainordre inner join ordre on (mainordre.ordrenr = ordre.ordrenr)');
Itemquery.SQL.Add('Where ordre.vnr =''' + Form1.MySQLQuery1.FieldByName('vnr').AsString + '''');
itemquery.SQL.Add('AND mainordre.status 4');
itemquery.SQL.Add('AND mainordre.status 5');
itemquery.SQL.Add('AND (mainordre.DeliveryDate = ''' + DateToStr(dato) + ''')');
ItemQuery.sql.EndUpdate;
itemquery.Active := True;

showmessage(itemquery.FieldByName('antal').AsString);
show antal as emty, antal is in ordre table

i put this in sqlYog (Got it from itemquery.sql.SaveToFile('c:\fgSQL.SQL'); )
SELECT * FROM mainordre INNER JOIN ordre ON (mainordre.ordrenr = ordre.ordrenr)
WHERE ordre.vnr ='300-YCB003'
AND mainordre.status 4
AND mainordre.status 5
AND (mainordre.DeliveryDate = '2011-07-29')

and antal is NOT emty

does unidac not support join?

sorry for my english

Kind Regards
Brian

AndreyZ · Post by **AndreyZ** » Wed 03 Aug 2011 07:50

Hello,

Please send a script to create and fill the mainordre and ordre tables (only the records which are selected using this query may be included in it) to andreyz*devart*com for investigation. Also please specify the following:
- the exact version of UniDAC. You can learn it from the About sheet of TUniConnection Editor;
- the exact version of your IDE;
- the exact version of MySQL server. You can learn it from the Info sheet of TUniConnection Editor.

vallemanden · Post by **vallemanden** » Wed 03 Aug 2011 11:27

AndreyZ wrote:Hello,

Please send a script to create and fill the mainordre and ordre tables (only the records which are selected using this query may be included in it) to andreyz*devart*com for investigation. Also please specify the following:
- the exact version of UniDAC. You can learn it from the About sheet of TUniConnection Editor;
- the exact version of your IDE;
- the exact version of MySQL server. You can learn it from the Info sheet of TUniConnection Editor.

sendt

AndreyZ · Post by **AndreyZ** » Wed 03 Aug 2011 14:07

I've checked your query. You have the antal field in both mainordre and ordre tables. That's why the antal field from the ordre table is called antal_1. You can check it by executing the following code:

Code: Select all

showmessage(itemquery.FieldByName('antal_1').AsString); // here you will see 1

vallemanden · Post by **vallemanden** » Fri 05 Aug 2011 09:09

thank you, that helped me

i normaly done like this

showmessage(itemquery.FieldByName('ordre.antal').AsString);
and i think thats more easy

if i want antal from mainordre then it is antal_0 ?

AndreyZ · Post by **AndreyZ** » Fri 05 Aug 2011 11:50

The antal field from the mainordre table is called antal.