I have two tables.
The first is for emitting a ticket and contains informations about the problem.
The second-one is for listing the employées.
In the first table I need two fields referring to the employées.
One for who made the ticket
A second-one for who had worked ont the ticket.
CREATE TABLE ticket_rep (
IdxTRep int(11) NOT NULL auto_increment,
IdxTUser int(11) NOT NULL default '1',
IdxTContact tinyint(4) NOT NULL default '1', <--- Here is the first contact
IdxTContact2 tinyint(4) NOT NULL default '1', <--- Here is the second contact
TRepTracking varchar(8) NOT NULL default '00000000',
TRepDateStart datetime NOT NULL default '0000-00-00 00:00:00',
TRepDateChange datetime NOT NULL default '0000-00-00 00:00:00',
TRepDateEnd datetime NOT NULL default '0000-00-00 00:00:00',
TRepFact varchar(64) NOT NULL default '',
TRepAchat varchar(32) NOT NULL default '',
TRepInventaire1 text NOT NULL,
TRepInventaire2 text NOT NULL,
TRepInventaire3 text NOT NULL,
TRepProbleme1 text NOT NULL,
TRepProbleme2 text NOT NULL,
TRepProbleme3 text NOT NULL,
TRepProbleme4 text NOT NULL,
TRepProbleme5 text NOT NULL,
TRepProbleme6 text NOT NULL,
TRepProbleme7 text NOT NULL,
TRepProbleme8 text NOT NULL,
TRepProbleme9 text NOT NULL,
TRepProbleme10 text NOT NULL,
TRepGarantie1 tinyint(1) NOT NULL default '0',
TRepGarantie2 tinyint(1) NOT NULL default '0',
TRepGarantie3 tinyint(1) NOT NULL default '0',
TRepGarantie4 tinyint(1) NOT NULL default '0',
TRepGarantie5 tinyint(1) NOT NULL default '0',
TRepGarantie6 tinyint(1) NOT NULL default '0',
TRepGarantie7 tinyint(1) NOT NULL default '0',
TRepGarantie8 tinyint(1) NOT NULL default '0',
TRepGarantie9 tinyint(1) NOT NULL default '0',
TRepGarantie10 tinyint(1) NOT NULL default '0',
TRepReparation1 text NOT NULL,
TRepReparation2 text NOT NULL,
TRepReparation3 text NOT NULL,
TRepReparation4 text NOT NULL,
TRepReparation5 text NOT NULL,
TRepReparation6 text NOT NULL,
TRepReparation7 text NOT NULL,
TRepReparation8 text NOT NULL,
TRepReparation9 text NOT NULL,
TRepReparation10 text NOT NULL,
TRepCout1 double(6,2) NOT NULL default '0.00',
TRepCout2 double(6,2) NOT NULL default '0.00',
TRepCout3 double(6,2) NOT NULL default '0.00',
TRepCout4 double(6,2) NOT NULL default '0.00',
TRepCout5 double(6,2) NOT NULL default '0.00',
TRepCout6 double(6,2) NOT NULL default '0.00',
TRepCout7 double(6,2) NOT NULL default '0.00',
TRepCout8 double(6,2) NOT NULL default '0.00',
TRepCout9 double(6,2) NOT NULL default '0.00',
TRepCout10 double(6,2) NOT NULL default '0.00',
IdxTStatus tinyint(4) NOT NULL default '1',
TRepCoutTotal double(8,2) NOT NULL default '0.00',
TRepEnabled enum('T','F') NOT NULL default 'T',
PRIMARY KEY (IdxTRep),
UNIQUE KEY TRepTracking (TRepTracking)
) TYPE=MyISAM;
CREATE TABLE ticket_contact (
IdxTContact int(11) NOT NULL auto_increment, <--- They should be reffering to this
TContactName varchar(255) NOT NULL default '',
TContactMail varchar(255) NOT NULL default '',
TContactTel varchar(255) NOT NULL default '',
PRIMARY KEY (IdxTContact)
) TYPE=MyISAM;
Now the question is : What is the join query that gives me the two names ?
This is my actual query :
SELECT `ticket_rep`.`IdxTRep`,
`ticket_rep`.`TRepTracking`,
`ticket_rep`.`TRepDateStart`,
`ticket_rep`.`TRepDateChange`,
`ticket_rep`.`TRepDateEnd`,
`ticket_rep`.`TRepFact`,
`ticket_rep`.`TRepAchat`,
`ticket_rep`.`TRepInventaire1`,
`ticket_rep`.`TRepInventaire2`,
`ticket_rep`.`TRepInventaire3`,
`ticket_rep`.`TRepProbleme1`,
`ticket_rep`.`TRepProbleme2`,
`ticket_rep`.`TRepProbleme3`,
`ticket_rep`.`TRepProbleme4`,
`ticket_rep`.`TRepProbleme5`,
`ticket_rep`.`TRepProbleme6`,
`ticket_rep`.`TRepProbleme7`,
`ticket_rep`.`TRepProbleme8`,
`ticket_rep`.`TRepProbleme9`,
`ticket_rep`.`TRepProbleme10`,
`ticket_rep`.`TRepGarantie1`,
`ticket_rep`.`TRepGarantie2`,
`ticket_rep`.`TRepGarantie3`,
`ticket_rep`.`TRepGarantie4`,
`ticket_rep`.`TRepGarantie5`,
`ticket_rep`.`TRepGarantie6`,
`ticket_rep`.`TRepGarantie7`,
`ticket_rep`.`TRepGarantie8`,
`ticket_rep`.`TRepGarantie9`,
`ticket_rep`.`TRepGarantie10`,
`ticket_rep`.`TRepReparation1`,
`ticket_rep`.`TRepReparation2`,
`ticket_rep`.`TRepReparation3`,
`ticket_rep`.`TRepReparation4`,
`ticket_rep`.`TRepReparation5`,
`ticket_rep`.`TRepReparation6`,
`ticket_rep`.`TRepReparation7`,
`ticket_rep`.`TRepReparation8`,
`ticket_rep`.`TRepReparation9`,
`ticket_rep`.`TRepReparation10`,
`ticket_rep`.`TRepCout1`,
`ticket_rep`.`TRepCout2`,
`ticket_rep`.`TRepCout3`,
`ticket_rep`.`TRepCout4`,
`ticket_rep`.`TRepCout5`,
`ticket_rep`.`TRepCout6`,
`ticket_rep`.`TRepCout7`,
`ticket_rep`.`TRepCout8`,
`ticket_rep`.`TRepCout9`,
`ticket_rep`.`TRepCout10`,
`ticket_rep`.`TRepCoutTotal`,
`ticket_rep`.`IdxTStatus`,
`ticket_user`.`TUserName`,
`ticket_user`.`TUserSurname`,
`ticket_user`.`TUserCompany`,
`ticket_user`.`TUserTel1`,
`ticket_user`.`TUserTel2`,
`ticket_user`.`TUserMobile`,
`ticket_user`.`TUserPrivate`,
`ticket_contact`.`TContactName`,
`ticket_contact`.`TContactMail`,
`ticket_status`.`TStatusName`
FROM `ticket_status`
INNER JOIN `ticket_rep` ON (`ticket_status`.`IdxTStatus` = `ticket_rep`.`IdxTStatus`)
INNER JOIN `ticket_user` ON (`ticket_rep`.`IdxTUser` = `ticket_user`.`IdxTUser`)
INNER JOIN `ticket_contact` ON (`ticket_rep`.`IdxTContact` = `ticket_contact`.`IdxTContact`)
WHERE `ticket_rep`.`TRepTracking`='$MyTrackingNumber'
Help !!! :-P
by: VGRPosted on 2003-05-22 at 07:08:26ID: 8564716
ARGH surtout pas ! ;-)
t AND a.TRepTracking=$MyTracking Number;
first your query ***should*** read as :
Then you lied : you've four tables ;-)
SELECT a.*, b.* FROM ticket_rep AS a, ticket_user AS b, ticket_status AS c, ticket_contact AS d WHERE c.IdxTStatus=a.IdxTStatus AND a.IdxTUser=b.IdxTUser AND d.IdxTContact=a.IdxTContac
1) no need of silly backquotes
2) no need of quotes around numerical values ($MyTrackingNumber)
3) I think you're wrong on the query.
if you just wanna the two names from first table based on the ID in the second, plus a given TRepTracking, then the query is ***much*** simplier.
tiens-moi au courant.