INTERSECT alternative solution

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • maddamon
    New Member
    • Jul 2007
    • 1

    #1

    INTERSECT alternative solution

    My problem looks like that:

    [CODE=sql]SELECT * FROM `table` where `attribute` = '24'
    INTERSECT
    SELECT * FROM `table` where `attribute` = '16';[/CODE]

    and my 'table' structure is : product_attribu tes(products_at tributes_id, products_id, options_id, options_values_ id, options_values_ price, price_prefix)

    I have this query and i`d like to have all products from the same table but with 2 different values and only with this values of 'attribute'.


    and here is full listing of query:

    [CODE=sql]select distinct p.products_imag e_xl_1, p.products_quan tity, p.products_mode l,

    p.products_weig ht, m.manufacturers _id, p.products_id, p.products_ship ping,

    p.products_date _available, pd.products_nam e, p.products_pric e, p.products_tax_ class_id,

    IF(s.status, s.specials_new_ products_price, NULL) as specials_new_pr oducts_price,

    IF(s.status, s.specials_new_ products_price, p.products_pric e) as final_price from products p

    left join manufacturers m using(manufactu rers_id) left join specials s on p.products_id =

    s.products_id left join products_attrib utes pa on p.products_id = pa.products_id,

    products_descri ption pd, categories c, products_to_cat egories p2c where p.products_stat us =

    '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id =

    p2c.products_id and p2c.categories_ id = c.categories_id and pa.options_valu es_id = '24'

    group by p.products_id

    INTERSECT

    select distinct p.products_imag e_xl_1, p.products_quan tity, p.products_mode l,

    p.products_weig ht, m.manufacturers _id, p.products_id, p.products_ship ping,

    p.products_date _available, pd.products_nam e, p.products_pric e, p.products_tax_ class_id,

    IF(s.status, s.specials_new_ products_price, NULL) as specials_new_pr oducts_price,

    IF(s.status, s.specials_new_ products_price, p.products_pric e) as final_price from products p

    left join manufacturers m using(manufactu rers_id) left join specials s on p.products_id =

    s.products_id left join products_attrib utes pa on p.products_id = pa.products_id,

    products_descri ption pd, categories c, products_to_cat egories p2c where p.products_stat us =

    '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id =

    p2c.products_id and p2c.categories_ id = c.categories_id and pa.options_valu es_id = '16'

    group by p.products_id

    order by final_price , products_name[/CODE]

    Any suggestions for that problem ??
    Thanks - maddamon
    Last edited by mwasif; Jul 26 '07, 05:46 PM. Reason: Added code tags.
  • mwasif
    Recognized Expert Contributor
    • Jul 2006
    • 802

    #2
    I hope these will help you to solve the problem.


    Comment

    Working...