none
Query for one row

    Question

  • Hi,

    I need help abt query.
    to simplify say I have a table like this

    Game_Table

    Match_ID    Team1   Team2    Match_date
       1               101    102         11.11.2011
       2               104    110         12.11.2011



    SO i want to make a query which tells me team 101 and team 102 is in the same row.

    I mean i need a query which will tell me this

    IF (team1 and team2 has same Match_ID) { 
    return true;
    }
    else {
    return false;
    }
    Saturday, July 27, 2013 6:35 AM

Answers

All replies

  • Something like this might work...
    IF EXISTS (
    	SELECT * FROM Game_Table 
    	WHERE (Team1 = 101 AND Team2 = 102)
    	OR    (Team1 = 102 AND Team2 = 101)
    )
    SELECT 'Yep, those teams are in the same row' as 'Result'
    ELSE
    SELECT 'Nope, those teams are not in the same row' as 'Result'


    Saturday, July 27, 2013 6:42 AM
  • the table which you have posted itself says that team1 column and team 2 column always going to have same match id as they are in the same row.

    Could you provide more detail on your question



    Mark ANSWER if this reply resolves your query, If helpful then VOTE HELPFUL
    Everything about SQL Server | Experience inside SQL Server -Mohammad Nizamuddin

    Saturday, July 27, 2013 6:45 AM
  • yes, but i actually planning another query so thats why i need to test if team1 and team2 has same matchID

    for example think like there is a player table and every player has team

    if there is a match between two teams i do not want team players to talk each other. 

    but if their teams do not have match against each other they will be able to talk. 

    thats why i need that query. 

    I tried this but did not work :(

    IF EXISTS (
    	SELECT * FROM Game_Table 
    	WHERE (Team1 = 101 AND Team2 = 102)
    	OR    (Team1 = 102 AND Team2 = 101)
    )
    SELECT 'Yep, those teams are in the same row' as 'Result'
    ELSE
    SELECT 'Nope, those teams are not in the same row' as 'Result'

    Saturday, July 27, 2013 7:24 AM
  • Do you have any more condition (may be mach date) ?

    consider the below scenario, the team 101 has two matches. So which match id should be considered in the query? May be you are thinking in the opposite way.

    You could so this way. take today's date and the match ids and then those teams cant talk to each other, rather than query starting from team id.

    Match_ID    Team1   Team2    Match_date
       1               101    102         11.11.2011
       2               104    110         12.11.2011
       3               101    103         15.11.2011
       4               103    105         18.11.2011

    I hope you got this


    Satheesh

    Saturday, July 27, 2013 12:38 PM
    Answerer
  • CREATE TABLE #t(id INT not null primary key,team1 INT, team2 INT)

    INSERT INTO #t VALUES (1,101,102)
    INSERT INTO #t VALUES (2,104,110)


    DECLARE @team1 INT,@team2 INT

    SET @team1 =101
    SET @team2=102

    SELECT DISTINCT CASE WHEN (SELECT id FROM #t WHERE team1=@team1)=
    (SELECT id FROM #t WHERE team2=@team2) THEN 'true' ELSE 'false' END
    FROM #t

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, July 28, 2013 8:52 AM
    Answerer
  • Hi Matthew,

    As SDeering post, it can work on our computer and our SQL Server version is SQL Server 2008 R2. So now we need to verify the mean of ”it does not work”. If it could not get the expected results when you perform the code, we need to check the structure of your table and so on. But if it just returns some errors , we can search the error message in our forum, or post the error message here and I will help to troubleshoot the issue.

    Thanks,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Monday, July 29, 2013 3:44 AM
    Moderator