SELECT statement

[Simon]

I have 3 tables:

table countryPrice:

productID countryId price
1 Italy 90
1 England 110

Table country:
countryId countryName

1 Italy
2 England
3 Germany

Table product:

productId productName productPrice
1 Book 100
2 Computer 1000
. ...... ...

Now, I have for example product Book.
I have to get the price for Book for each country, if the price doesn't
exist for that
country, the default price from table product should be used.

So, I need select statement, which returns the result for product Book:

countryName price
Italy 90
England 110
Germany 100

Does anybody know the select statement?

Thank you,
Simon

[Simon]

I have 3 tables:

table countryPrice:

productID countryId price
1 Italy 90
1 England 110

Table country:
countryId countryName

1 Italy
2 England
3 Germany

Table product:

productId productName productPrice
1 Book 100
2 Computer 1000
. ...... ...

Now, I have for example product Book.
I have to get the price for Book for each country, if the price doesn't
exist for that
country, the default price from table product should be used.

So, I need select statement, which returns the result for product Book:

countryName price
Italy 90
England 110
Germany 100

Does anybody know the select statement?

Thank you,
Simon

[Ray Higdon]

How do you join books with countryprice table? Sounds
like using CASE would work.

Ray Higdon MCSE, MCDBA, CCNA

>-----Original Message-----
>I have 3 tables:
>
>table countryPrice:
>
>productID countryId price
> 1 Italy 90
> 1 England 110
>
>Table country:
>countryId countryName
>
> 1 Italy
> 2 England
> 3 Germany
>
>Table product:
>
>productId productName productPrice
> 1 Book 100
> 2 Computer 1000
> . ...... ...
>
>Now, I have for example product Book.
>I have to get the price for Book for each country, if

the price doesn't

>exist for that
>country, the default price from table product should be

used.

>
>So, I need select statement, which returns the result

for product Book:

>
>countryName price
> Italy 90
> England 110
> Germany 100
>
>Does anybody know the select statement?
>
>Thank you,
>Simon
>
>
>
>.
>

[Anthony Faull]

SELECT country.countryName, countryPrice.price
FROM product
INNER JOIN countryPrice on countryPrice.productID = product.productID
INNER JOIN country on country.countryID = countryPrice.countryID
WHERE product.productName = 'Book'

"Simon" <simon.zupan@stud-moderna.si> wrote in message
news:3f02a59b$1@news.s5.net...

> I have 3 tables:
>
> table countryPrice:
>
> productID countryId price
> 1 Italy 90
> 1 England 110
>
> Table country:
> countryId countryName
>
> 1 Italy
> 2 England
> 3 Germany
>
> Table product:
>
> productId productName productPrice
> 1 Book 100
> 2 Computer 1000
> . ...... ...
>
> Now, I have for example product Book.
> I have to get the price for Book for each country, if the price doesn't
> exist for that
> country, the default price from table product should be used.
>
> So, I need select statement, which returns the result for product Book:
>
> countryName price
> Italy 90
> England 110
> Germany 100
>
> Does anybody know the select statement?
>
> Thank you,
> Simon
>
>
>

[David Portas]

CREATE TABLE Countries (countryid INTEGER PRIMARY KEY, countryname
VARCHAR(20) NOT NULL UNIQUE)
INSERT INTO Countries VALUES (1, 'Italy')
INSERT INTO Countries VALUES (2, 'England')
INSERT INTO Countries VALUES (3, 'Germany')

CREATE TABLE Products (productid INTEGER PRIMARY KEY, productname
VARCHAR(20) NOT NULL UNIQUE, productprice NUMERIC(10,2) NOT NULL CHECK
(productprice>0))
INSERT INTO Products VALUES (1, 'Book', 100)
INSERT INTO Products VALUES (2, 'Computer', 1000)

CREATE TABLE CountryPrice (productid INTEGER NOT NULL REFERENCES Products
(productid), countryid INTEGER NOT NULL REFERENCES Countries (countryid),
price NUMERIC(10,2) NOT NULL CHECK (price>0), PRIMARY
KEY(productid,countryid))
INSERT INTO CountryPrice VALUES (1,1,90)
INSERT INTO CountryPrice VALUES (1,2,110)

SELECT P.productid, P.productname, C.countryname,
COALESCE(R.price,P.productprice) AS price
FROM Products AS P
CROSS JOIN Countries AS C
LEFT JOIN CountryPrice AS R
ON P.productid = R.productid AND C.countryid = R.countryid
WHERE P.productid = 1

--
David Portas
------------
Please reply only to the newsgroup
--

[David Portas]

Doesn't help where the price is missing for the country though.

--
David Portas
------------
Please reply only to the newsgroup
--

[Peter Bengtsson]

Hi
Try this one:
Select c.CountryName, isnull(cp.Price, p.ProdPrice) from
Country c
LEFT JOIN CountryPrice cp
on cp.CountryID = c.CountryID and cp.ProductID = 1
, Product p where p.ProductID = 1

Drwback: You have to use ProdID twice...

>-----Original Message-----
>I have 3 tables:
>
>table countryPrice:
>
>productID countryId price
> 1 Italy 90
> 1 England 110
>
>Table country:
>countryId countryName
>
> 1 Italy
> 2 England
> 3 Germany
>
>Table product:
>
>productId productName productPrice
> 1 Book 100
> 2 Computer 1000
> . ...... ...
>
>Now, I have for example product Book.
>I have to get the price for Book for each country, if the

price doesn't

>exist for that
>country, the default price from table product should be

used.

>
>So, I need select statement, which returns the result for

product Book:

>
>countryName price
> Italy 90
> England 110
> Germany 100
>
>Does anybody know the select statement?
>
>Thank you,
>Simon
>
>
>
>.
>

[David Portas]

This works but I suggest you avoid mixing SQL89 and SQL92 JOIN syntax in the
same query. Use CROSS JOIN instead so that the join criteria is unambiguous.

SELECT c.CountryName, ISNULL(cp.Price, p.ProdPrice)
FROM Country c
LEFT JOIN CountryPrice cp
ON cp.CountryID = c.CountryID AND cp.ProductID = 1
CROSS JOIN Product p
WHERE p.ProductID = 1

--
David Portas
------------
Please reply only to the newsgroup
--