DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: SQL Help

  1. #1
    Join Date
    Mar 2008
    Posts
    11

    SQL Help

    Hello,
    I need to combine the following two parts. It would basically add the two queries. The queries work fine by themselves but it fails when combined (I don't know how to combine them). So if I could get some help in combining these two I would really appreciate it. Thanks

    sql.doc

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Both SQL statements are Identical, I see nothing to combine; but I did notice a possible error in your logic. The 'And' Operator takes presidence over the 'Or' operator so based on Your original statement:
    Code:
    SELECT Count(*) AS [Count]
    FROM Tbl_Work
    WHERE [2007]='Full - Time' OR [2007]='Part - Time' AND StartYear=2007 AND Starts='Spring';
    Count all records where [2007]='Full - Time' and also count the records where [2007]='Part - Time' And StartYear=2007 And Starts='Spring'

    This could produce a very defferent result then if you add Parentheses and force a different grouping like this:
    Code:
    SELECT Count(*) AS [Count]
    FROM Tbl_Work
    WHERE ([2007]='Full - Time' OR [2007]='Part - Time') AND StartYear=2007 AND Starts='Spring';
    Count all records where [2007] must have a value of either 'Full - Time' or 'Part - Time' but each record must also have StartYear=2007 and Starts='Spring'

    I am not sure which way you wanted it to work; but I thought I would point out the difference.

  3. #3
    Join Date
    Mar 2008
    Posts
    11
    I'm sorry about that. The SQL statements are different. Here they are

    sql[1].doc

    What it basically needs to do is to add up the two different situations defined by WHERE " ". Both get the same data from the same table.

  4. #4
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Try This:
    Code:
    SELECT Count(*) AS [Count]
    FROM Tbl_Work
    WHERE ([2007]='Full - Time' OR [2007]='Part - Time') AND (StartYear=2007 Or StartYear=200) AND (Starts='Spring' Or Starts='Fall');

  5. #5
    Join Date
    Mar 2008
    Posts
    11
    Yes that is what I wanted, and that does work
    I also want to be able to combine two of these in a query (I don't know if this would be a subquery) What is the best way to do this. The queries of course would be for different years (ex. WHERE [2006]='Full - Time' OR [2006]='Part - Time'................)

  6. #6
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    You could use a summary query. Add Year to the Selection and then Group By year. For example if we use your StartYear, We group by it and find a range like all records from 2005 to 2007. Your SQL would look like this:
    Code:
    SELECT StartYear, Count(*) AS [Count]
    FROM Tbl_Work
    WHERE ([2007]='Full - Time' OR [2007]='Part - Time') 
    AND (StartYear>=2005 And StartYear<=2007) 
    AND (Starts='Spring' Or Starts='Fall')
    GROUP BY StartYear;
    Now if you need to select by a hard coded field name like [2007] then you would have to use a union query, something like this:
    Code:
    SELECT  2007 As Year, Count(*) AS [Count]
    FROM Tbl_Work
    WHERE ([2007]='Full - Time' OR [2007]='Part - Time') 
    AND StartYear=2007 
    AND (Starts='Spring' Or Starts='Fall')
    UNION SELECT  2006 As Year, Count(*) AS [Count]
    FROM Tbl_Work
    WHERE ([2006]='Full - Time' OR [2006]='Part - Time') 
    AND StartYear=2006 
    AND (Starts='Spring' Or Starts='Fall')
    UNION SELECT  2005 As Year, Count(*) AS [Count]
    FROM Tbl_Work
    WHERE ([2005]='Full - Time' OR [2005]='Part - Time') 
    AND StartYear=2005 
    AND (Starts='Spring' Or Starts='Fall')
    In a Union query each query must have the same fields in the select clause so I hard coded the year value and gave it the name 'Year'.
    The results of this union query will be 3 records where Year will be one of 2005, 2006, or 2007 and Count will be the count for that year.
    Notice each Select statement will return one record for it's selected year.

Similar Threads

  1. Access to SQL server
    By Nate in forum Database
    Replies: 29
    Last Post: 05-09-2001, 10:04 AM
  2. Deadlock error.. how to remove
    By Chandra in forum VB Classic
    Replies: 0
    Last Post: 06-22-2000, 12:52 PM
  3. Please help me -- urgent -- deadlock error
    By chandra in forum VB Classic
    Replies: 0
    Last Post: 06-22-2000, 07:36 AM
  4. Re: ODBC error
    By Devaraj in forum Enterprise
    Replies: 0
    Last Post: 05-11-2000, 12:48 PM
  5. Re: Referential Integrity- New to SQL Server
    By Aaron in forum VB Classic
    Replies: 0
    Last Post: 03-17-2000, 04:24 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