| Author |
 Topic  |
|
|
user
Starting Member
36 Posts |
 Posted - 30 April 2004 : 14:15:04
|
I have three tables as model a, model_upc b, agencypower c. The structure is the following
table:Model as a
a.model_seq_num
a.boxlable_model_desc
a.partnumber
a.agencypower_id1
a.agencypower_id2
table:Model_UPC as b
Model_seq_num
UPCCode
table:Agencypower C
Agencypower_id
Agencypower_desc
Query I wrote:
select a.model_seq_number, a.boxlabel_model_desc, a.agencylabel_model_desc,a.partnumber, a.upd_by, a.upd_time, b.UPCCode,b.upd_by as requester, b.upd_time as DateIssued, c.agencypower_desc from model a, model_upc b, agencypower c where a.model_seq_number=b.model_seq_number and
a.agencypower_id1=c.agencypower_id
The query above would give me all I want, except for the agencypower_desc corresponding to agencypower_id2. How do I modify this query to get that result as well.
Any help is highly appreciated!
|
Edited by - user on 30 April 2004 14:16:44 |
|
|
user
Starting Member
36 Posts |
Posted - 30 April 2004 : 16:40:38
|
I found the answer.
The following query works. Posting it here if in case anyone needs it.
----------------------
select a.model_seq_number, a.boxlabel_model_desc, a.agencylabel_model_desc,a.partnumber, a.upd_by, a.upd_time, b.UPCCode,b.upd_by as requester, b.upd_time as DateIssued, c.agencypower_desc, d.agencypower_desc from model a, model_upc b, agencypower c, agencypower d where a.model_seq_number=b.model_seq_number and a.agencypower_id1=c.agencypower_id and
a.agencypower_id2 = d.agencypower_id
|
Edited by - user on 30 April 2004 16:41:04 |
 |
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 01 May 2004 : 07:21:44
|
Nice work.
Couple of tips for you here to make SQL easier:
Give you Queries more structure:
SELECT
a.model_seq_number,
a.boxlabel_model_desc,
a.agencylabel_model_desc,
a.partnumber,
a.upd_by,
a.upd_time,
b.UPCCode,
b.upd_by as requester,
b.upd_time as DateIssued,
c.agencypower_desc,
d.agencypower_desc
FROM
model a,
model_upc b,
agencypower c,
agencypower d
WHERE
a.model_seq_number = b.model_seq_number
AND a.agencypower_id1 = c.agencypower_id
AND a.agencypower_id2 = d.agencypower_id
also, when using table aliases, give them useful names like,
SELECT
mdl.model_seq_number,
mdl.boxlabel_model_desc,
mdl.agencylabel_model_desc,
mdl.partnumber,
mdl.upd_by,
mdl.upd_time,
mdlu.UPCCode,
mdlu.upd_by as requester,
mdlu.upd_time as DateIssued,
ap.agencypower_desc,
ap2.agencypower_desc
FROM
model mdl,
model_upc mdlu,
agencypower ap,
agencypower ap2
WHERE
mdl.model_seq_number = mdlu.model_seq_number
AND mdl.agencypower_id1 = ap.agencypower_id
AND mdl.agencypower_id2 = ap2.agencypower_id
|
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
Edited by - D3mon on 01 May 2004 07:28:35 |
|
|
|
user
Starting Member
36 Posts |
Posted - 04 May 2004 : 15:08:51
|
| Thank You! |
|
|
|
user
Starting Member
36 Posts |
Posted - 04 May 2004 : 15:10:01
|
Hi D3Mon,
Thank You for your suggestions.
|
Edited by - user on 04 May 2004 15:10:39 |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
|
| |
Topic |
|
|
|
Topic Locked
