khaled salem
asked on
update joined tables
Dear Expert:
Iam using Delphi 2010 & MySQL, in my system there is seven tables as following:
1- Foretrans table, sn field is PK contains all transaction of ticketing information.
2- foretax table contains the tax details of the row in the main table (Foretrans) joined by USN field is FK.
3- foredetails table contains the route details of the ticket.
4- ticketypes is table joined with foredetails on (foredetails.tktype=ticket ypes.tktyp e)
the following lines script to create table and fill with necessary values
My Questions are :
1- when vendor id (glaccno) displayed in dbcombobox how to change the combobox of vendorname. and how can i change the vendor id via changing vendorName
Iam using Delphi 2010 & MySQL, in my system there is seven tables as following:
1- Foretrans table, sn field is PK contains all transaction of ticketing information.
2- foretax table contains the tax details of the row in the main table (Foretrans) joined by USN field is FK.
3- foredetails table contains the route details of the ticket.
4- ticketypes is table joined with foredetails on (foredetails.tktype=ticket
the following lines script to create table and fill with necessary values
CREATE TABLE `vendors` (
`GLaccno` varchar(20) NOT NULL,
`vendorName` varchar(100) DEFAULT NULL,
`officecomm` decimal(8,3) DEFAULT '0.000',
`agentcomm` decimal(8,3) DEFAULT '0.000',
PRIMARY KEY (`GLaccno`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `Agencies` (
`Genaccno` varchar(20) NOT NULL,
`AgentName` varchar(100) DEFAULT NULL,
`officecomm` decimal(8,3) DEFAULT '0.000',
`agentcomm` decimal(8,3) DEFAULT '0.000',
PRIMARY KEY (`Genaccno`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `services` (
`servcode` int(11) DEFAULT NULL,
`Servname` varchar(50) DEFAULT NULL,
PRIMARY KEY (`servcode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `ticketypes` (
`tktype` int(11) DEFAULT NULL,
`tktname` varchar(30) DEFAULT NULL,
PRIMARY KEY (`tktype`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `foretrans` (
`sn` int(11) NOT NULL ,
`xdate` date DEFAULT NULL,
`vendglaccno` int(11) DEFAULT NULL,
`clientglaccno` int(11) DEFAULT NULL,
`srvscode` int(11) DEFAULT NULL,
`airlineid` varchar(3) DEFAULT NULL,
`docno` varchar(20) DEFAULT NULL,
`status` varchar(3) DEFAULT NULL,
`paxname` varchar(35) DEFAULT NULL,
`price` decimal(18,3) DEFAULT '0.000',
PRIMARY KEY (`sn`),
UNIQUE KEY `srchindex` (`docno`,`status`,`airlineid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `foretax` (
`USN` int(11) NOT NULL,
`airlineid` varchar(3) NOT NULL,
`docno` varchar(20) NOT NULL,
`status` varchar(3) NOT NULL,
`yq` decimal(18,3) DEFAULT '0.000',
`lyimprint` decimal(18,3) DEFAULT '0.000',
`xt` decimal(18,3) DEFAULT '0.000',
`cp` decimal(18,3) DEFAULT '0.000',
PRIMARY KEY (`USN`),
UNIQUE KEY `tktsrch` (`airlineid`,`status`,`docno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `foredetails` (
`USN` int(11) NOT NULL,
`airlineid` varchar(3) NOT NULL,
`docno` varchar(20) NOT NULL,
`status` varchar(3) NOT NULL,
`r1` varchar(3) DEFAULT NULL,
`r2` varchar(3) DEFAULT NULL,
`r3` varchar(3) DEFAULT NULL,
`tktype` int(1) unsigned DEFAULT '0',
`tktclass` varchar(2) DEFAULT NULL,
`flightno` varchar(6) DEFAULT NULL,
`papertkt` int(1) unsigned DEFAULT '0',
PRIMARY KEY (`USN`),
UNIQUE KEY `tktscrch` (`airlineid`,`status`,`docno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into vendors (GLaccno,Vendorname,officecomm,agentcomm) Values ('2001','Wings Airlines',5,0);
insert into vendors (GLaccno,Vendorname,officecomm,agentcomm) Values ('2002','easyjet Airlines',5,0);
insert into vendors (GLaccno,Vendorname,officecomm,agentcomm) Values ('2003','Rayan Airlines',5,0);
insert into agencies (Genaccno,AgentName,officecomm,agentcomm) Values ('101','Plastic Factory',0,0);
insert into agencies (Genaccno,AgentName,officecomm,agentcomm) Values ('102','Moon Salon',0,0);
insert into ticketypes (tktype,tktname) values (1, 'Paper');
insert into ticketypes (tktype,tktname) values (2, 'Eletronic');
insert into ticketypes (tktype,tktname) values (3, 'SMS');
insert into services (servcode,Servname) VALUES (1, 'Ticket');
insert into services (servcode,Servname) VALUES (2, 'Hotel');
insert into services (servcode,Servname) VALUES (3, 'Insurance');
insert into foretrans (sn,xdate,vendglaccno,clientglaccno,srvscode,airlineid,docno,status,paxname,price)
VALUES (8801,'2017-01-01','2002','101',1,'176','2400123','NW','John Smith',150);
insert into foredetails (USN,airlineid,docno,status,r1,r2,r3,tktype,tktclass,flightno,papertkt)
VALUES (8801,'176','2400123','NW','TIP','AMM','SAN',1,'Y','420',0);
insert into foretax (USN,airlineid,docno,status,yq,lyimprint,xt,cp)
VALUES (881,'176','2400123','NW',25,15,1,5);
insert into foretrans (sn,xdate,vendglaccno,clientglaccno,srvscode,airlineid,docno,status,paxname,price)
VALUES (8802,'2017-01-01','2001','101',1,'148','2400188','EX','William Alexander',220);
insert into foredetails (USN,airlineid,docno,status,r1,r2,r3,tktype,tktclass,flightno,papertkt)
VALUES (8802,'148','2400188','EX','SAN','TUN','IST',0,'W','580',1);
insert into foretax (USN,airlineid,docno,status,yq,lyimprint,xt,cp)
VALUES (8802,'148','2400188','EX',25,15,1,5);
insert into foretrans (sn,xdate,vendglaccno,clientglaccno,srvscode,airlineid,docno,status,paxname,price)
VALUES (8803,'2017-01-01','2002','102',1,'512','2400414','NW','Sara Jack',97);
insert into foredetails (USN,airlineid,docno,status,r1,r2,r3,tktype,tktclass,flightno,papertkt)
VALUES (8803,'512','2400414','NW','ABA','LAB','BEN',0,'L','125',0);
insert into foretax (USN,airlineid,docno,status,yq,lyimprint,xt,cp)
VALUES (8803,'512','2400414','NW',17,0,14.25,5.5);
insert into foretrans (sn,xdate,vendglaccno,clientglaccno,srvscode,airlineid,docno,status,paxname,price)
VALUES (8804,'2017-01-01','2003','103',1,'220','2400987','RF','Arvid Rajif',140.50);
insert into foredetails (USN,airlineid,docno,status,r1,r2,r3,tktype,tktclass,flightno,papertkt)
VALUES (8804,'220','2400987','RF','DZJ','MIR','DZJ',1,'Y','420',0);
insert into foretax (USN,airlineid,docno,status,yq,lyimprint,xt,cp)
VALUES (8804,'220','2400987','RF',17.25,22,1,15);
The following query to display the ticket:Select a.sn, a.xdate, a.vendglaccno,a.clientglaccno,a.srvscode,a.airlineid, a.docno,a.status,a.paxname, a.price,
e.r1,e.r2,e.r3,e.tktype,e.tktclass,e.flightno,e.papertkt,
f.yq ,f.lyimprint ,f.xt, f.cp
FROM foretrans a
inner join vendors b on a.vendglaccno=b.glaccno
inner join agencies C on a.clientglaccno=c.genaccno
inner join services d on a.srvscode=d.servcode
left join foredetails e on a.sn=e.usn
left join foretax f on a.sn=f.usn
where a.sn= 8802
the result of query displayed in DBtext, DBComboboxMy Questions are :
1- when vendor id (glaccno) displayed in dbcombobox how to change the combobox of vendorname. and how can i change the vendor id via changing vendorName
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.