locked
SQL query to find out if two values are different RRS feed

  • Question

  • User-1188570427 posted

    I have the following table:

    ConnectionTable

    -------------

    ConnectionId - Guid

    -------------------------------

    ScheduleTable

    --------------

    ConnectionId - Guid (FK to ConnectionId of ConnectionTable)

    GameDayNumber - Int (will be one or two)

    -------------------------------

    I want a query that will display ConnectionIds that are the same in ScheduleTable but have DIFFERENT GameDayNumbers?

    What would that query look like? Thanks.

    Here is the LINQ equivalent that works for what I want etc.:

            public List<Guid> FindPostponedGameIdGameDayNumberOutOfSync()
            {
                var mainPostponedGameIdList = Context.SCHEDULE_POSTPONED_INFO.Select(s => s.POSTPONED_GAME_ID).ToList();
    
                var outOfSyncGameDayNumberPostponedGameIdList = new List<Guid>();
    
                foreach(var item in mainPostponedGameIdList)
                {
                    var listOfGameDayNumbers = Context.CHEDULE_INFO.Where(w => w.CONNECTED_GAME_ID == item).Select(s => s.GAME_DAY_NBR).ToList();
    
                    if (listOfGameDayNumbers.Count == 2)
                    {
                        if(listOfGameDayNumbers.FirstOrDefault() != listOfGameDayNumbers.LastOrDefault())
                        {
                            outOfSyncGameDayNumberPostponedGameIdList.Add(item);
                        }
                    }
                    else
                    {
                        outOfSyncGameDayNumberPostponedGameIdList.Add(item);
                    }
                }
    
                return outOfSyncGameDayNumberPostponedGameIdList;
            }

    Wednesday, May 2, 2018 3:10 AM

Answers

  • User-369506445 posted

    hi

    I wrote a script for you , that find all id rows are same with diff GameDayNumber, please try below script,

    SELECT distinct t.ConnectionId 
      FROM [dbo].[ConnectionTable] ct
      join [dbo].[ScheduleTable] t on ct.ConnectionId=t.ConnectionId
      where exists(select top 1 * from [dbo].[ScheduleTable] where [dbo].[ScheduleTable].ConnectionId=t.ConnectionId and [dbo].[ScheduleTable].GameDayNumber!=t.GameDayNumber)
      
      

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 2, 2018 5:18 AM

All replies

  • User-369506445 posted

    hi

    I wrote a script for you , that find all id rows are same with diff GameDayNumber, please try below script,

    SELECT distinct t.ConnectionId 
      FROM [dbo].[ConnectionTable] ct
      join [dbo].[ScheduleTable] t on ct.ConnectionId=t.ConnectionId
      where exists(select top 1 * from [dbo].[ScheduleTable] where [dbo].[ScheduleTable].ConnectionId=t.ConnectionId and [dbo].[ScheduleTable].GameDayNumber!=t.GameDayNumber)
      
      

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, May 2, 2018 5:18 AM
  • User-1188570427 posted

    hi

    I wrote a script for you , that find all id rows are same with diff GameDayNumber, please try below script,

    SELECT distinct t.ConnectionId 
      FROM [dbo].[ConnectionTable] ct
      join [dbo].[ScheduleTable] t on ct.ConnectionId=t.ConnectionId
      where exists(select top 1 * from [dbo].[ScheduleTable] where [dbo].[ScheduleTable].ConnectionId=t.ConnectionId and [dbo].[ScheduleTable].GameDayNumber!=t.GameDayNumber)
      
      

    Exactly was what I was looking for. Thank you.

    Wednesday, May 2, 2018 10:24 AM