Hi All,
I have the following query:
SELECT mi.name, mi.item_id, mpd.quantity, mpd.unit_price, mpd.id, mcv.custom FROM m_pur_det mpd JOIN m_item mi ON mpd.item_id = mi.id LEFT JOIN m_cust_var mcv ON mpd.id=mcv.detail_id WHERE mpd.purchase_id = 79072;
Details of the tables:
m_item : PK- id
Column | Type | Modifiers
mer_id | integer |
name | character varying(20) |
description | character varying(250) |
price | integer |
max_quantity | integer |
id | integer | not null default nextval
('public.m_item_id_seq'::text)
item_id | character varying(10) |
m_pur_det: PK -id
Column | Type | Modifiers
purchase_id | integer | not null
item_id | integer | not null
quantity | integer | not null
id | integer | not null default nextval
('public.m_pur_det_id_seq'::text)
unit_price | integer | not null
Indexes: m_pur_det_pkey primary key btree (id),
m_pur_det_purchase_id_key unique btree (purchase_id, item_id)
FK constraints: (item_id) REFERENCES m_item(id)
m_cust_var : PK - id
Column | Type | Modifiers
id | integer | not null default nextval
('public.m_cust_var_id_seq'::text)
detail_id | integer |
custom | character varying(100) |
Indexes: m_cust_var_pkey primary key btree (id),
m_cust_var_detail_id_key unique btree (detail_id)
Foreign Key: (detail_id) REFERENCES m_pur_det(id)
Now my question:
I would like to know if there is a way to re-write the query using sub-select. And will that improve the performance??
Thanks,
Saranya
Do you Yahoo!?
The
all-new My Yahoo! � Get yours free!
