CODEWITHSUNDEEP
sqlinformix
tck
tck

Reputation: 3

Informix: Query merge

i have 2 queries: 

select COUNT(o.id_offer) from offers o, product p where
 p.id_product = "+ productID +" and o.id_offer = "+ offerID +" and (b.type = 0 or o.type = "A")

this query returns the count of some product

select p.id_product, p.name, s.id_supplier, s.name from product p, suppliers s where p.id_supplier = s.id_supplier

this query returns all products data

i want to combine the two queries in one, something like this:

select (select COUNT(o.id_offer) from offers o, product p where
 p.id_product = p.id_product and o.id_offer = 13345 and (b.type = 0 or o.type = "A")) count,p.id_product, p.name, s.id_supplier, s.name from product p, suppliers s where p.id_supplier = s.id_supplier

Anyone knows how to do this in informix?

Upvotes: 0

Views: 168

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 755054

Please use the explicit join notation, not the comma-list of table names in the FROM clause notation.

The first query needs generalizing to generate a product ID and the matching count.

SELECT p.id_product, COUNT(o.id_offer) AS offer_count
  FROM offers o
  JOIN product p ON p.id_product = o.id_product -- Guessed column
 WHERE (p.type = 0 OR o.type = 'A')
 GROUP BY p.id_product

The second query can be converted to:

SELECT p.id_product, p.name, s.id_supplier, s.name
  FROM product p
  JOIN suppliers s ON p.id_supplier = s.id_supplier

These two queries can be combined with a join, too:

SELECT x.id_product, x.product_name, x.id_supplier, x.supplier_name, y.offer_count
  FROM (SELECT p.id_product, p.name, s.id_supplier, s.name
          FROM product p
          JOIN suppliers s ON p.id_supplier = s.id_supplier
       ) AS x
  JOIN (SELECT p.id_product, COUNT(o.id_offer) AS offer_count
          FROM offers o
          JOIN product p ON p.id_product = o.id_product -- Guessed column
         WHERE (p.type = 0 OR o.type = 'A')
         GROUP BY p.id_product
       ) AS y
    ON x.id_product = y.id_product

Upvotes: 1

Related Questions