/    Sign up×
Community /Pin to ProfileBookmark

mysql query problem

Copy linkTweet thisAlerts:
Jun 06.2005

let say there is a 3 tables. table1, table2 and table3.

table1 have pkey, name(foreign key to table3)

table2 have pkey, new_name(foreign key to table3), old_name(foreign key to table3)

table 3 have pkey, name_id, name_description(primary)

how do i select from here to show all the data? because right now i m doing on a report writer which confuse me a lots.

[code=php]
“select name as name_Table1 , new_name as newname_Table2 , old_name as oldname_table2 from table1 left join table3 on table3.pkey = table1.name left join table2 left join table3 on table3.pkey = table1.name”;
[/code]

to post a comment
PHP

4 Comments(s) ↴

Copy linkTweet thisAlerts:
@superman101589Jun 06.2005 — can you be a little more specific on what you are trying to do? are these tables or rows ON a table?
Copy linkTweet thisAlerts:
@Stephen_PhilbinJun 06.2005 — SELECT * FROM table1,table2,table3;

Is the very rough way to do it.

Give us the create table query and we can give you a better query.
Copy linkTweet thisAlerts:
@beginnerzauthorJun 07.2005 — [code=php]
CREATE TABLE employee (
pkey int(11) NOT NULL auto_increment,
fullname varchar(50) default NULL,
oldnric varchar(8) default NULL,
newnric varchar(14) default NULL,
address int(11) default NULL,
gender char(1) NOT NULL default 'M',
dob date default NULL,
age int(11) NOT NULL default '0',
pob varchar(50) default NULL,
race int(11) NOT NULL default '1',
religion int(11) NOT NULL default '1',
bloodtype varchar(4) default NULL,
maritalstatus varchar(10) NOT NULL default 'SINGLE',
citizenship int(11) NOT NULL default '1',
drivinglicense varchar(20) default NULL,
lastmodified timestamp(14) NOT NULL,
status int(11) NOT NULL default '1',
photograph text,
PRIMARY KEY (pkey),
KEY address (address),
KEY race (race),
KEY religion (religion),
KEY citizenship (citizenship)
) TYPE=InnoDB;



CREATE TABLE department (
pkey int(11) NOT NULL auto_increment,
deptid varchar(10) NOT NULL default '',
deptdescr varchar(50) default NULL,
PRIMARY KEY (pkey),
UNIQUE KEY deptid (deptid)
) TYPE=InnoDB;


CREATE TABLE promotion (
pkey int(11) NOT NULL auto_increment,
employee int(11) NOT NULL default '0',
promotiondate date NOT NULL default '0000-00-00',
currentdesignation int(11) NOT NULL default '0',
newdesignation int(11) NOT NULL default '0',
department int(11) NOT NULL default '1',
division int(11) NOT NULL default '1',
note varchar(100) default NULL,
PRIMARY KEY (pkey),
KEY employee (employee),
KEY currentdesignation (currentdesignation),
KEY newdesignation (newdesignation),
KEY department (department),
KEY division (division)
) TYPE=InnoDB;

[/code]


my problem now is how can i using one mysql query to execute the things that i want.

for example,

SELECT p1.designationdescr as Current_Designation,p2.designationdescr as New_Designation FROM employee e LEFT JOIN promotion promo ON promo.employee = e.pkey ,designation as p1, designation as p2 where p1.pkey = promo.currentdesignation and p2.pkey = promo.newdesignation;

this query is working jes fine...but is not the things that i want...i wan it to show all employee data which have include promotion info at the back. the query above is select where p1.pkey = promo.currentdesignation and p2.pkey = promo.newdesignation which only will show the employee who have promotion.
Copy linkTweet thisAlerts:
@beginnerzauthorJun 07.2005 — SELECT

transactions.ID,

transactions.ProdID,

transactions.RatePlan,

transactions.ServArea,

products.ID as prodID,

products.Name as prodName,

rateplans.ID as rateID,

rateplans.Name as rateName,

servarea.ID as servID,

servarea.Name as servName

FROM carts

/*----------------------------------------------*/

LEFT OUTER JOIN rateplans

ON rateplans.ID = transactions.RatePlan

LEFT OUTER JOIN servarea

ON servarea.ID = transactions.ServArea

INNER JOIN products

ON products.ID = transactions.ProdID

INNER JOIN transactions

ON transactions.CartID = carts.ID AND

carts.ID = 'the cart id im tracking'

/*----------------------------------------------*/


correct? something like that?
×

Success!

Help @beginnerz spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.20,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...