Results 1 to 12 of 12
  1. #1
    Join Date
    May 2004
    Posts
    13

    Unanswered: Need to make this query

    Hi guys, I have a car_race table which has these fields

    car_id int
    race_id int
    b_car_won varchar // can have 'y' or 'n'

    I need to know if the car lost the 1st race but won the next race

    And example of that table for car_id 1:

    car_id 1
    race_id 1
    b_car_won 'NO'

    car_id 1
    race_id 2
    b_car_won 'YES'

    Now this is the tricky part, the database has some data integrity issues, so this can occur:

    car_id 1
    race_id 1
    b_car_won 'NO'

    car_id 1
    race_id 3
    b_car_won 'YES'

    So I cant used a fixed race_id value, need to use the race_id > 1 to know whats the next race. But this raises another issue if I have this in the database:

    car_id 1
    race_id 1
    b_car_won 'NO'

    car_id 1
    race_id 3
    b_car_won 'YES'

    car_id 1
    race_id 4
    b_car_won 'YES'

    If I query I'd get 2 rows where race_id > 1. And I only need the first one, because 3 is the next race.

    I need to fetch in a single row if possible, the result of the 1st race and the 2nd race. How can I do this?

  2. #2
    Join Date
    Feb 2004
    Posts
    134
    Quote Originally Posted by Zero_Gravity
    Hi guys, I have a car_race table which has these fields

    car_id int
    race_id int
    b_car_won varchar // can have 'y' or 'n'
    First, why use a varchar and not a bit field for this b_car_won?

    Now this is the tricky part, the database has some data integrity issues, so this can occur:

    car_id 1
    race_id 1
    b_car_won 'NO'

    car_id 1
    race_id 3
    b_car_won 'YES'
    What is wrong with above that makes it tricky? If you make car_id, race_id, and b_car_won the primary key together, this would be all the integrity constraints you would need.

    So I cant used a fixed race_id value, need to use the race_id > 1 to know whats the next race. But this raises another issue if I have this in the database:

    car_id 1
    race_id 1
    b_car_won 'NO'

    car_id 1
    race_id 3
    b_car_won 'YES'

    car_id 1
    race_id 4
    b_car_won 'YES'
    Are you using parameters in your queries?

    Code:
    declare @iRace int,
    SET @iRace = 1
    
    SELECT race_id AS Race,
               SUM(CASE car_id = 1 THEN b_car_won ELSE 0) AS Car 1,
               SUM(CASE car_id = 2 THEN b_car_won ELSE 0) AS Car 2,
               SUM(CASE car_id = 3 THEN b_car_won ELSE 0) AS Car 3
               .....
               .....
    FROM tbRaces 
    WHERE race_e >= @iRace AND race_e <= @iRace + 1
    GROUP BY race_id
    
    should result in something like
    -------------------------------------------------------------
    Race       | Car 1 | Car 2  | Car 3 | .......
    -------------------------------------------------------------
    1            | 1      | 0        | 0
    2            | 1      | 0        | 0
    Playing with it a bit you could probably through in another case statement to change 1 = 'YES' and 0 = 'No'

    Nothing above is tested, just MHO

    Mike B

  3. #3
    Join Date
    May 2004
    Posts
    13
    Actually I need the opposite, for a given car, display 1st race results and next race results, where next race is > 1.


    CAR_ID 1ST_RACE NEXT_RACE
    Car 1 | Y | Y
    Car 2 | N | N

    NEXT_RACE: can be 2, 3 or 4. Because the 2nd or 3rd race information might no be present on database.
    Last edited by Zero_Gravity; 06-29-04 at 17:50.

  4. #4
    Join Date
    Feb 2004
    Posts
    134
    Quote Originally Posted by Zero_Gravity
    Actually I need the opposite, for a given car, display 1st race results and next race results, where next race is > 1.


    1ST_RACE NEXT_RACE
    Car 1 Y Y
    Car 2 N N

    NEXT_RACE: can be 2, 3 or 4. Because the 2nd or 3rd race information might no be present on database.
    Would the following work?
    Code:
    USE NORTHWIND
    
    CREATE TABLE tbRaces (
    race_id int,
    car_id int,
    b_car_won bit
    )
    
    GO
    
    INSERT INTO tbRaces (race_id, car_id, b_car_won)
    SELECT 1, 1, 0 UNION ALL
    SELECT 1, 2, 0 UNION ALL 
    SELECT 1, 3, 1 UNION ALL
    SELECT 2, 1, 1 UNION ALL
    SELECT 2, 2, 0 UNION ALL
    SELECT 2, 3, 0 UNION ALL
    SELECT 3, 1, 1 UNION ALL
    SELECT 3, 2, 0 UNION ALL
    SELECT 3, 3, 0
    
    GO
    
    DECLARE @iRaceID int
    SET @iRaceID = 2
    
    SELECT A.car_id,
    	CASE WHEN A.ThisRace = 0 THEN 'N' ELSE 'Y' END AS ThisRace,
    	CASE WHEN A.NextRace = 0 THEN 'N' ELSE 'Y' END AS NextRace
    FROM	(SELECT car_id,
    		SUM(CASE WHEN race_id = @iRaceID AND b_car_won = 1 THEN 1 ELSE 0 END) AS ThisRace,
    		SUM(CASE WHEN race_id = @iRaceID + 1 AND b_car_won = 1 THEN 1 ELSE 0 END) AS NextRace
    		FROM tbRaces
    	WHERE race_id >= @iRaceID AND race_id <= @iRaceID + 1
    	GROUP BY car_id) A
    GO
    
    DROP TABLE tbRaces
    GO
    I ran this in QA and it seems to be what you want!

    You could then write an SQL statement to look for the "Next race" after the one requested.

    Eg:
    Code:
    DECLARE @iRaceID int, @iNextRace int
    SET @iRaceID = 1
    
    SELECT @iNextRace = TOP 1 race_id FROM tbRaces 
    WHERE race_id > @iRaceID
    ORDER BY race_id ASC
    So, if the user request race 1 and there is no 2 & 3 but there is a 4, the next race would be 4. Then change the

    "WHERE race_id >= @iRaceID AND race_id <= @iRaceID + 1"

    TO

    "WHERE race_id = @iRaceID AND race_id = @iNextRace"

    HTH
    Mike B
    Last edited by MikeB_2k4; 06-29-04 at 18:00.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What grade did you get?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Posts
    134
    Quote Originally Posted by Brett Kaiser
    What grade did you get?
    What do you mean?

    ike B
    Last edited by MikeB_2k4; 06-30-04 at 11:07.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    So if a car participated in 4 races, which ones do you want back? 1st and last? Or 1st and 2nd? And what do you want to get if a car didn't win a single race? To be excluded from the results completely?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Brett thinks you may be asking us to help you on a homework assignment, because of the general nature of your question.

    I suspected as much too, but then I saw you have over one hundred posts. Most homework-help seekers are first-time posters.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    I need help with some homework. Do you guys know who ruled Spain during the event that provoked the painting of Guernica? Also, what does it represent and what event provoked it? Any takers?

    This should be an interesting SELECT statemetn.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Uhmmm...

    SELECT Fascist from EuropeanRulers where Name = 'Franco'?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    COO. Did you just know that, or did you look it up?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes I knew it, but Franco is the ONLY Spanish leader I could name.

    But I am familiar with the history of Guernica and its bombing by German planes. Its a very powerful painting.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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