Professional Web Applications Themes

SELECT statement - Microsoft SQL / MS SQL Server

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? ...

  1. #1

    Default SELECT statement

    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 Guest

  2. #2

    Default SELECT statement

    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 Guest

  3. #3

    Default SELECT statement

    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
    >
    >
    >
    >.
    >
    Ray Higdon Guest

  4. #4

    Default Re: SELECT statement

    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.zupanstud-moderna.si> wrote in message
    news:3f02a59b$1news.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
    >
    >
    >

    Anthony Faull Guest

  5. #5

    Default Re: SELECT statement


    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 Guest

  6. #6

    Default Re: SELECT statement

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

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



    David Portas Guest

  7. #7

    Default SELECT statement

    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
    >
    >
    >
    >.
    >
    Peter Bengtsson Guest

  8. #8

    Default Re: SELECT statement

    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
    --



    David Portas Guest

  9. #9

    Default Select Statement

    I have 3 tables (Site, Patron, and ShipInfo). Site has a
    SiteID, and a ShipInfoID. Patron has a PatronID, SiteID,
    and a ShipInfoID. As you can see, Site and Patron are
    maped, and individually Site maps to ShipInfo and Patron
    maps to ShipInfo. Only one of the tables (Site or
    Patron) will have a ShipInfoID NOT NULL. Given a SiteID
    I need a SELECT statement that says "If the site has a
    ShipInfoID Not NULL, then return the corresponding row
    from ShipInfo Table, else If NULL then take the
    Site.SiteID, map to the Patron table, find that
    ShipInfoID, and return the corresponding row from
    ShipInfo Table.

    The tables are already constructed and populated, I just
    need the correct Select Statement. Can someone help me
    with this?

    Thanks much!
    Mike Guest

  10. #10

    Default Re: Select Statement

    Try:

    SELECT A.SHIPINFOIDID
    FROM SHIPINFO a INNER JOIN SITE B ON A.SHIPINFOID=B.SHIPINFOID
    WHERE B.SHIPINFOID IS NOT NULL
    UNION ALL
    SELECT C.SHIPINFOIDID
    FROM PATRON a INNER JOIN SITE B ON A.SITEID=B.SITEID
    JOIN SHIPINFO C ON C.SHIPINFOID=A.SHIPINFOID
    WHERE B.SHIPINFOID IS NULL

    --
    -Vishal
    "Mike" <com> wrote in message
    news:09c401c34ad2$941a8760$gbl... 


    Vishal Guest

  11. #11

    Default Re: Select Statement

    What is the lowercase 'a' for in front of the INNER
    JOINS? And I'm not sure I understand what the A,B, and C
    are representing.


     
    A.SHIPINFOID=B.SHIPINFOID [/ref]
    has a [/ref]
    SiteID, [/ref]
    Patron [/ref]
    SiteID [/ref]
    just 
    >
    >
    >.
    >[/ref]
    Mike Guest

  12. #12

    Default Re: Select Statement

    Those are the alisases used for actual table names.

    --
    -Vishal
    "Mike" <com> wrote in message
    news:0ab701c34ad9$efad5a60$gbl... 
    > A.SHIPINFOID=B.SHIPINFOID [/ref]
    > has a [/ref]
    > SiteID, [/ref]
    > Patron [/ref]
    > SiteID [/ref]
    > just 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Vishal Guest

  13. #13

    Default Re: Select Statement

    Ok, I understand the alisases. I don't understand that
    second Select though, and it returns no results for me.
    I translated the alisases and I don't understand how you
    can say "Select ShipInfo.ShipInfoID from Patron". Does
    that work to return the ID from ShipInfo based on a
    Patron row? I wish I could explain why, but this still
    does not give me the correct results. Any ideas?


     [/ref]
    and C 
    >> A.SHIPINFOID=B.SHIPINFOID 
    >> has a 
    >> SiteID, [/ref][/ref]
    are 
    >> Patron 
    >> SiteID [/ref][/ref]
    has a [/ref][/ref]
    row 
    >> just [/ref][/ref]
    help me [/ref]
    >
    >
    >.
    >[/ref]
    Mike Guest

  14. #14

    Default Re: Select Statement

    yes, because it is included in the join
    The query will return the record after jioning patron's siteid and site's
    siteid matching rows and then it will have join
    on shipinfo table's shipinfoid field to be joined with patron table for
    matching shipinfoid for site table's shipinfoid column's values are null

    --
    -Vishal
    "Mike" <com> wrote in message
    news:066f01c34ade$be6dd510$gbl... [/ref]
    > and C [/ref]
    > are [/ref]
    > has a [/ref]
    > row [/ref]
    > help me 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Vishal Guest

  15. #15

    Default Re: Select Statement

    Thanks Vishal, I will see if i can get this working.
    Query Designer does not support the UNION SQL construct,
    so I guess there's no good way to test this before
    putting into code. The only other concern that I have is
    that the C# code that this SELECT will be put into has
    the SiteID given as a parameter so I need a specific row.


     
    siteid and site's 
    patron table for 
    values are null [/ref]
    you 
    >> and C [/ref][/ref]
    A.SITEID=B.SITEID [/ref][/ref]
    Site [/ref][/ref]
    Patron [/ref][/ref]
    or 
    >> has a [/ref][/ref]
    corresponding [/ref][/ref]
    populated, I 
    >> help me [/ref]
    >
    >
    >.
    >[/ref]
    mike Guest

  16. #16

    Default SELECT statement

    I have 2 tables, user and country AND I would like to SELECT all users from
    the country of current user (up_id=1):

    userTable
    ------------
    up_ID
    up_country_ID
    up_name
    up_address
    ........

    CountryTable:
    -----------------

    country_ID
    country_Name

    select up_name from userTable WHERE up_country_id=
    (SELECT c.country_id FROM CountryTable c INNER JOIN userTable u ON
    u.up_country_ID=c.country_ID WHERE u.up_id=1)

    This SELECT returns the result. Know I would like to know is there any
    better solution
    or my SELECT statement is fast enough? (up_ID and country_id are PK)

    Thank you,
    Simon



    Simon Guest

  17. #17

    Default SELECT statement

    try this one:

    select up_name from userTable WHERE up_country_id=
    (SELECT u.up_country_id FROM userTable u WHERE u.up_id=1)

     
    SELECT all users from 
    userTable u ON 
    is there any 
    country_id are PK) 
    Arun[Symbiosis] Guest

  18. #18

    Default Re: SELECT statement


    "Simon" <si> wrote in message
    news:3f1520b9$s5.net... 
    from 


    John Guest

  19. #19

    Default SELECT statement

    I have 2 tables: orders and orderProduct:

    Orders:

    orderId status

    orderProduct:

    orderId productId quantity date

    Now I must select the latest quantity of product, if status of order is 2
    or the second latest quantity of product if the status of order is 3.

    For example:

    orderId status
    1 2
    2 3

    orderId productId quantity date
    1 1 3 18/07/2003
    1 1 2 17/07/2003
    2 2 4 18/07/2003
    2 2 1 17/07/2003
    2 2 5 16/07/2003
    .................
    ................


    So my result should be:

    productId quantity
    1 3
    2 1
    .........
    .........

    Thank you for your answer,
    Simon


    Simon Guest

  20. #20

    Default Re: SELECT statement

    "Simon" <si> wrote in message news:3f2e1def$s5.net... 

    CREATE TABLE Orders
    (
    orderid INT NOT NULL PRIMARY KEY,
    status INT NOT NULL
    )

    INSERT INTO Orders (orderid, status)
    VALUES (1, 2)
    INSERT INTO Orders (orderid, status)
    VALUES (2, 3)

    CREATE TABLE OrderProduct
    (
    orderid INT NOT NULL REFERENCES Orders (orderid),
    productid INT NOT NULL,
    quantity INT NOT NULL,
    date SMALLDATETIME NOT NULL,
    PRIMARY KEY (orderid, date)
    )

    INSERT INTO OrderProduct (orderid, productid, quantity, date)
    VALUES (1, 1, 3, '20030718')
    INSERT INTO OrderProduct (orderid, productid, quantity, date)
    VALUES (1, 1, 2, '20030717')
    INSERT INTO OrderProduct (orderid, productid, quantity, date)
    VALUES (2, 2, 4, '20030718')
    INSERT INTO OrderProduct (orderid, productid, quantity, date)
    VALUES (2, 2, 1, '20030717')
    INSERT INTO OrderProduct (orderid, productid, quantity, date)
    VALUES (2, 2, 5, '20030716')

    SELECT OP1.*
    FROM OrderProduct AS OP1
    INNER JOIN
    Orders AS O
    ON OP1.orderid = O.orderid AND
    ((O.status = 2 AND
    1 = (SELECT COUNT(*)
    FROM OrderProduct AS OP2
    WHERE OP1.orderid = OP2.orderid AND
    OP2.date >= OP1.date)) OR
    (O.status = 3 AND
    2 = (SELECT COUNT(*)
    FROM OrderProduct AS OP2
    WHERE OP1.orderid = OP2.orderid AND
    OP2.date >= OP1.date)))
    ORDER BY OP1.orderid

    orderid productid quantity date
    1 1 3 2003-07-18 00:00:00
    2 2 1 2003-07-17 00:00:00

    Regards,
    jag


    John Guest

Page 1 of 2 12 LastLast

Similar Threads

  1. select statement woes
    By megalith in forum Coldfusion Database Access
    Replies: 8
    Last Post: March 27th, 09:07 PM
  2. sql select case statement
    By Anderson11983 in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 9th, 01:50 PM
  3. Enter Select Statement?
    By Hung Kuen Kung Fu in forum Dreamweaver AppDev
    Replies: 0
    Last Post: March 22nd, 09:30 PM
  4. SP with Select statement
    By Gerald in forum ASP Database
    Replies: 3
    Last Post: November 27th, 03:38 PM
  5. help with SELECT statement
    By Chris Hohmann in forum ASP
    Replies: 2
    Last Post: August 19th, 05:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139