CODEWITHSUNDEEP
sql
DPERROTT
DPERROTT

Reputation: 51

passing a parameter into a subquery

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

Upvotes: 0

Views: 32909

Answers (6)

Shiraj Momin
Shiraj Momin

Reputation: 695

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

Upvotes: 0

Zephyr
Zephyr

Reputation: 7823

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.

Upvotes: 7

thomaux
thomaux

Reputation: 19738

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

Upvotes: 0

Jagmag
Jagmag

Reputation: 10366

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

Upvotes: 1

vc 74
vc 74

Reputation: 38179

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

or am I missing something?

Upvotes: 5

Numenor
Numenor

Reputation: 1706

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

Upvotes: 0

Related Questions