passing a parameter into a subquery

Question

Hi,

i was wondering if it is possible to pass a parameter into a select subquery.

What i want to do is collect some product data from one table and then crossref the weight of the item to it's carriage cost in a shipping table to return a cost.

something like:

select cataloguenumber, productname, 
      (select shipping.carriagecost 
       from shipping 
       where shipping.carriageweight = weight) as carriagecost
from products

Regards

DPERROTT

Answer 1

Your subquery should only return 1 row, if it returns more then that your query will throw an error in run-time.

Answer 2

select cataloguenumber, productname,  shipping.carriagecost as carriagecost 
from products, shipping 
where shipping.carriageweight = products.weight

or am I missing something?

Answer 3

SELECT DISTINCT cataloguenumber, productname, shipping.carriagecost 
FROM products
LEFT OUTER JOIN shipping 
ON shipping.carriageweight = products.weight

Answer 4

This is possible I think, but then you should retrieve the column you want to pass in your parent query.

select cataloguenumber, productname, weight 
      (select shipping.carriagecost 
       from shipping 
       where shipping.carriageweight = weight) as carriagecost
from products

Answer 5

While the subquery would work, a better, more readable and efficient way to define this would be as follows:

SELECT  p.cataloguenumber
 ,      p.productname,  
 ,      s.carriagecost    
FROM    products p
    INNER JOIN
        shipping s
    ON  p.weight = s.carriageweight

This assumes that all product weights have a corresponding entry in the shipping table. If that is not the case then change from INNER JOIN to LEFT JOIN and deal with any nulls.