Tough SQL query


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Tough SQL query

  1. #1
    Join Date
    Apr 2007
    Posts
    1

    Tough SQL query

    Ok I have the following data stored in a table and I need to query it, the problem is I need to get multiple combinations for all 3 fields. Not sure if I can do this with sql or whether I need to code it programmatically, hope someone can help. My data is as follows


    id bid price1 price2 price3
    -- --- ------ ------ ------
    1 1 1.20 3.00 2.20
    2 1 1.15 3.10 0.00
    3 1 1.18 3.20 2.30

    bid represents an id that groups these prices together so for bid 1 I need the following results.

    price1 price2 price3
    ------ ------ ------
    1.20 3.00 2.20
    1.20 3.00 2.30
    1.20 3.10 2.20
    1.20 3.10 2.30
    1.20 3.20 2.20
    1.20 3.20 2.30
    1.15 3.00 2.20
    1.15 3.00 0.00
    1.15 3.00 2.30
    1.15 3.10 2.20
    1.15 3.10 0.00
    1.15 3.10 2.30
    1.15 3.20 2.20
    1.15 3.20 0.00
    1.15 3.20 2.30
    1.18 3.00 2.20
    1.18 3.00 2.30
    1.18 3.10 2.20
    1.18 3.10 2.30
    1.18 3.20 2.20
    1.18 3.20 2.30

    The rules for the results we see here are as follows

    1. If a row has a price3 > 0 get the other combos of price2 and price3 to go with price1
    2. If a row has a price3 = 0 get the other combos of price2 and price3 but also get the combos on price1 and price2 assuming price3 is 0

    I was trying to use a self join 3 times somthing like

    select a.price1, b.price2, c.price3 from test a, test b, test c where a.bid = b.bid and a.bid = c.bid

    but I can't figure out a way to exclude the 0 I don't want and to include the extra 0's I need. Can someone advise if this is even possible to do with an sql query.

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    What is the query you are using now?

  3. #3
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    I think the db design may need to be optimized as basic db normalization rules state that no field can be dependupon another field in the same table.

    I think you might need to place all prices on the same record so when you retrieve the record you can easily evcal which field to use.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  4. #4
    Join Date
    Apr 2007
    Posts
    2

    query against sub-query

    You can go ahead and use your multiple self-join approach, but need to use it as a sub-query. Let it return all price combinations including zero prices.

    Then, in the main query you can set the criteria that all price fields need to be greater than zero. Be sure to use either the distinct keyword or the group by clause to eliminate duplicate records. See both examples below.


    select distinct price1, price2, price3
    from (
    select a.price1, b.price2, c.price3
    from test a, test b, test c
    where a.bid = b.bid and a.bid = c.bid
    )
    where price1 > 0
    and price2 > 0
    and price3 > 0
    order by price1, price2, price3

    OR

    select price1, price2, price3
    from (
    select a.price1, b.price2, c.price3
    from test a, test b, test c
    where a.bid = b.bid and a.bid = c.bid
    )
    where price1 > 0
    and price2 > 0
    and price3 > 0
    group by price1, price2, price3
    order by price1, price2, price3


    Hope this helps.

Similar Threads

  1. Tough SQL query / procedure needed
    By nimmow in forum Database
    Replies: 1
    Last Post: 02-11-2006, 07:42 PM
  2. SQL2000 remote SQL query
    By lightningtechie in forum Database
    Replies: 1
    Last Post: 02-07-2006, 09:34 AM
  3. Return Query Estimate for SQL Server 2000
    By Daniel Reber in forum VB Classic
    Replies: 0
    Last Post: 09-16-2002, 03:50 PM
  4. SQL Query Fails on NT System
    By Ken D. in forum VB Classic
    Replies: 2
    Last Post: 09-04-2002, 12:25 AM
  5. IIF in SQL Query Analyzer
    By Jeff Johnson in forum Database
    Replies: 3
    Last Post: 04-30-2001, 03:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center