4 Replies Latest reply: May 2, 2013 1:12 PM by Frank Kulash RSS

    Why "No data found" with this INNER JOIN?

    1006779
      Guess I still don't understand joins but I can't wrap my head around why this doesn't return any data. I have inserted data that makes "Bonus" foreign keys "Site_Name" the same as the primary key of "Poker_Site" "Site_Name";



      SELECT "Poker_Site"."site_name", "Bonus"."bonus_name"
           FROM "Poker_Site"
           INNER JOIN "Bonus"
           ON "Poker_Site"."site_name"="Bonus"."bonus_name";


      CREATE TABLE "Poker_Site" (

      "site_name" NVARCHAR2(50) NOT NULL,

      "site_start" DATE NOT NULL,

      "coutry_origin" VARCHAR2(70) NULL,

      "Description " VARCHAR2(500) NULL,

      PRIMARY KEY ("site_name")

      );



      CREATE TABLE "Bonous" (

      "bonus_name" NVARCHAR2(100) NOT NULL,

      "sign_up_bouns" NVARCHAR2(50) NULL,

      "rack_back" NUMBER(10) NULL,

      "site_name" NVARCHAR2(50) NULL,

      PRIMARY KEY ("bonus_name")

      );

      ALTER TABLE "Bonous" ADD CONSTRAINT "fk_Bonous_Poker_Site_1" FOREIGN KEY ("site_name") REFERENCES "Poker_Site" ("site_name");
        • 1. Re: Why "No data found" with this INNER JOIN?
          sb92075
          1003776 wrote:
          Guess I still don't understand joins but I can't wrap my head around why this doesn't return any data. I have inserted data that makes "Bonus" foreign keys "Site_Name" the same as the primary key of "Poker_Site" "Site_Name";



          SELECT "Poker_Site"."site_name", "Bonus"."bonus_name"
               FROM "Poker_Site"
               INNER JOIN "Bonus"
               ON "Poker_Site"."site_name"="Bonus"."bonus_name";


          CREATE TABLE "Poker_Site" (

          "site_name" NVARCHAR2(50) NOT NULL,

          "site_start" DATE NOT NULL,

          "coutry_origin" VARCHAR2(70) NULL,

          "Description " VARCHAR2(500) NULL,

          PRIMARY KEY ("site_name")

          );



          CREATE TABLE "Bonous" (

          "bonus_name" NVARCHAR2(100) NOT NULL,

          "sign_up_bouns" NVARCHAR2(50) NULL,

          "rack_back" NUMBER(10) NULL,

          "site_name" NVARCHAR2(50) NULL,

          PRIMARY KEY ("bonus_name")

          );

          ALTER TABLE "Bonous" ADD CONSTRAINT "fk_Bonous_Poker_Site_1" FOREIGN KEY ("site_name") REFERENCES "Poker_Site" ("site_name");
          I suggest that you refrain from ever using double quote marks with Oracle;
          unless & until you actually know what you are doing with them.

          using COPY & PASTE post the SQL & results that report is a problem
          • 2. Re: Why "No data found" with this INNER JOIN?
            Frank Kulash
            Hi,

            Please take a few minutes now to read the forum FAQ {message:id=9360002}. It's an excellent investment that can help you a lot and save you a lot of time.
            1003776 wrote:
            Guess I still don't understand joins but I can't wrap my head around why this doesn't return any data. I have inserted data
            Post a complete test script that people can run to re-create the problem and test their solutions. Include the INSERT statements.
            Did you COMMIT the transaction? Are you running the query in the same database session, or a different session?
            that makes "Bonus" foreign keys "Site_Name" the same as the primary key of "Poker_Site" "Site_Name";

            SELECT "Poker_Site"."site_name", "Bonus"."bonus_name"
                 FROM "Poker_Site"
                 INNER JOIN "Bonus"
                 ON "Poker_Site"."site_name"="Bonus"."bonus_name";
            I don't see anything obviously wrong in the query itself.
            When you post the INSERT statements, also post the results you expect from that data.
            CREATE TABLE "Poker_Site" (

            "site_name" NVARCHAR2(50) NOT NULL,

            "site_start" DATE NOT NULL,

            "coutry_origin" VARCHAR2(70) NULL,

            "Description " VARCHAR2(500) NULL,

            PRIMARY KEY ("site_name")

            );
            As mentioned already, double-quotes are just asking from trouble. Sometimes you get what you ask for.
            Don't use double-quotes around table- or column names:
            CREATE TABLE Poker_Site 
            (
                site_name    NVARCHAR2(50)   NOT NULL,
                site_start       DATE            NOT NULL,
            ...
            );
            >
            >
            CREATE TABLE "Bonous" (

            "bonus_name" NVARCHAR2(100) NOT NULL,

            "sign_up_bouns" NVARCHAR2(50) NULL,

            "rack_back" NUMBER(10) NULL,

            "site_name" NVARCHAR2(50) NULL,

            PRIMARY KEY ("bonus_name")

            );
            This is not the same table referenced in the query.
            This table is named
            "Bonous" (6 characters, including 2 'o's). The table in the query is
            "Bonus" (5 characters, only 1 'o').
            ALTER TABLE "Bonous" ADD CONSTRAINT "fk_Bonous_Poker_Site_1" FOREIGN KEY ("site_name") REFERENCES "Poker_Site" ("site_name");
            • 3. Re: Why "No data found" with this INNER JOIN?
              1006779
              Recreated the database without quotes. The problem still persists.

              Query

              SELECT Poker_Site.site_name, Bonus.bonus_name
              FROM Poker_Site
              INNER JOIN Bonus
              ON Poker_Site.site_name=Bonus.bonus_name;

              Example of one corresponding data entry.

              INSERT INTO Bonus
              VALUES ('888 Bonus','Double up to 400$','36','888');

              INSERT INTO Poker_Site
              VALUES ('888','01-06-2002','Gibraltar','888poker.com is Cassavas online poker room branch. It was launched in July 2002 and focuses exclusively on variants of poker and other card games. Traditional poker variants are offered, including Texas Holdem, Omaha, and Seven-card stud. 888 poker offers free play versions of its games for players who prefer not to risk real money. 888 Poker is Microsoft Windows and Mac compatible. Players can choose to play on the sites software program or on the non-downloadable game version available online.');



              CREATE TABLE Poker_Site (

              site_name NVARCHAR2(50) NOT NULL,

              site_start DATE NOT NULL,

              coutry_origin VARCHAR2(70) NULL,

              Description VARCHAR2(500) NULL,

              PRIMARY KEY ( site_name )

              );



              CREATE TABLE Bonus (

              bonus_name NVARCHAR2(100) NOT NULL,

              sign_up_bouns NVARCHAR2(50) NULL,

              rack_back NUMBER(10) NULL,

              site_name NVARCHAR2(50) NULL,

              PRIMARY KEY ( bonus_name )

              );

              ALTER TABLE Bonus ADD CONSTRAINT fk_Bonus_Poker_Site_1 FOREIGN KEY ( site_name ) REFERENCES Poker_Site ( site_name );
              • 4. Re: Why "No data found" with this INNER JOIN?
                Frank Kulash
                Hi,
                1003776 wrote:
                Recreated the database without quotes. The problem still persists.
                Please post a complete test script that people can run to re-create the problem and test their ideas.
                Put all the statements in the correct order. For example, the query should come after the INSERT statements, and the INSERT statements should come after the CREATE TABLE statements.
                Query

                SELECT Poker_Site.site_name, Bonus.bonus_name
                FROM Poker_Site
                INNER JOIN Bonus
                ON Poker_Site.site_name=Bonus.bonus_name;

                Example of one corresponding data entry.

                INSERT INTO Bonus
                VALUES ('888 Bonus','Double up to 400$','36','888');

                INSERT INTO Poker_Site
                VALUES ('888','01-06-2002','Gibraltar','888poker.com is Cassavas online poker room branch. It was launched in July 2002 and focuses exclusively on variants of poker and other card games. Traditional poker variants are offered, including Texas Holdem, Omaha, and Seven-card stud. 888 poker offers free play versions of its games for players who prefer not to risk real money. 888 Poker is Microsoft Windows and Mac compatible. Players can choose to play on the sites software program or on the non-downloadable game version available online.');
                Site_start is a DATE. Don't try to INSERT a VARCHAR2 value, such as '01-06-2002' into a DATE column. Use TO_DATE to convert a VARCHAR2 into a DATE, or use a DATE literal.
                CREATE TABLE Poker_Site (

                site_name NVARCHAR2(50) NOT NULL,

                site_start DATE NOT NULL,

                coutry_origin VARCHAR2(70) NULL,

                Description VARCHAR2(500) NULL,

                PRIMARY KEY ( site_name )

                );



                CREATE TABLE Bonus (

                bonus_name NVARCHAR2(100) NOT NULL,

                sign_up_bouns NVARCHAR2(50) NULL,

                rack_back NUMBER(10) NULL,

                site_name NVARCHAR2(50) NULL,

                PRIMARY KEY ( bonus_name )

                );

                ALTER TABLE Bonus ADD CONSTRAINT fk_Bonus_Poker_Site_1 FOREIGN KEY ( site_name ) REFERENCES Poker_Site ( site_name );
                The constraint guarantees that, if there is a value in bonus.site_name, it will match a value in poker_site.site_name.

                The join condition of the query
                ON Poker_Site.site_name=Bonus.bonus_name;
                doesn't reference bonus.site_name; it uses a different column.
                In this sample data, poker_site.site_name is
                '888' (3 characters), but bonus.bonus_name is
                '888 Bonus' (9 characters). No 3-character string is equal to any 9-character string. Either you meant to use different columns, or you meant to use some other functions and/or operatoirs in the join condition.

                If you change the join condition to
                ON  Poker_Site.site_name = Bonus.site_name
                or to
                ON  Bonus.bonus_name  LIKE Poker_Site.site_name || '%'
                then you'll get 1 row of output.

                Edited by: Frank Kulash on May 2, 2013 2:06 PM