steps to create dynamic join query in oracle sql [message #668714] |
Mon, 12 March 2018 07:02 |
|
asheolik
Messages: 1 Registered: March 2018
|
Junior Member |
|
|
Hi SQL GURUS
I am doing this query for creating testing set of data.
Need your help :
select distinct fixed_atribute_element_id from products p
inner join product_attributes using(product_id)
left join
( select attribute_element_id as
fixed_atribute_element_id,product_attribute_id from attribute_fixed_texts
where attribute_element_id = 510
)using (product_attribute_id)
where attribute_type_id = 159
i will need this one for sample testing data . But issue is table can have multiple fixed text data that may come from another table.
which in turn should look like this :
select distinct fixed_atribute_element_id,fixed_atribute_element_id1 from
products p
inner join product_attributes using(product_id)
left join
( select attribute_element_id as
fixed_atribute_element_id,product_attribute_id from attribute_fixed_texts
where attribute_element_id = 510
)using (product_attribute_id)
left join
(
select attribute_element_id as
fixed_atribute_element_id1,product_attribute_id from attribute_free_texts
where attribute_element_id = 509
) using(product_attribute_id )
where attribute_type_id = 159
I thought this can be done by generating dynamic join query.
Can you please give guidance .How can i achieve this.This is just sample.
Please let me know i can clarify further.
|
|
|
Re: steps to create dynamic join query in oracle sql [message #668716 is a reply to message #668714] |
Mon, 12 March 2018 08:04 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Welcome to the forum. Please read the OraFAQ Forum Guide and How to use code tags and make your code easier to read
I've formatted your first query for you:
SELECT DISTINCT fixed_atribute_element_id
FROM products p
inner join product_attributes USING(product_id)
left join (SELECT attribute_element_id AS fixed_atribute_element_id,
product_attribute_id
FROM attribute_fixed_texts
WHERE attribute_element_id = 510)USING (product_attribute_id)
WHERE attribute_type_id = 159 It would help if you used table aliases correctly. As it is, one has to guess where each column comes from. Do you actually need to include PRODUCTS in the query?
|
|
|