Web Analytics Made Easy -
StatCounter inner join - CodingForum

Announcement

Collapse
No announcement yet.

inner join

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • inner join

    Hi,I have two tables first one consist of four columns like below

    Country Table

    id:
    code:USA
    name:United States
    the other tables


    The other one consist of 5 columns

    People table
    id:
    firstname:ali
    Lastname :ak
    Bio:student
    countryId:1


    What I want to do is to write sql statments that joins two tables for example first name from people table and his/her country name from the other table

  • #2
    SELECT firstname, name FROM People INNER JOIN Country ON countryId=Country.id
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

    Comment


    • #3
      Originally posted by felgall View Post
      SELECT firstname, name FROM People INNER JOIN Country ON countryId=Country.id
      Almost. You are going to get an ambiguous error in the ON clause. You need to specify the table names or alias in the ON clause.


      SELECT firstname, name FROM People INNER JOIN Country ON People.countryId=Country.Country.id
      Last edited by benanamen; Sep 22, 2016, 07:54 PM.
      To save time, lets just assume I am almost never wrong.

      The XY Problem
      The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

      Make A Donation https://www.paypal.me/KevinRubio

      Comment


      • #4
        Originally posted by benanamen View Post
        Almost. You are going to get an ambiguous error in the ON clause.
        Why - countryId is unique to one table so it doesn't need the table prefix. I did specify the table prefix for the id field which is not unique - you added a database prefix which will only work if the database is called Country.
        Stephen
        Learn Modern JavaScript - http://javascriptexample.net/
        Helping others to solve their computer problem at http://www.felgall.com/

        Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

        Comment


        • #5
          Ding Ding... Round one to benanamen.. round two to felgall.

          Who's right?



          I'm off to the bookies to put my money on felgall...
          "Tango says double quotes with a single ( ' ) quote in the middle"
          '$Name says single quotes with a double ( " ) quote in the middle'
          "Tango says double quotes ( \" ) must escape a double quote"
          '$Name single quotes ( \' ) must escape a single quote'

          Comment


          • #6
            thank you all

            Comment


            • #7
              EDIT: I just looked at OP'S tables again.....

              Felgal's query will work. IMO, not the best way to do it though.


              you added a database prefix which will only work if the database is called Country.
              No, they are both table prefixes. If it was as you say it would be DB_name.Country.Country.id

              IMHO it is more clear to us the table.column_name on both ON clause columns, especially when you start doing complicated queries. If you were joining multiple tables you would have no idea where the one ON clause is coming from.

              What threw me off is the way OP does his id naming from how I do it. He is using two different names in his tables for the same id, one being "id", the other countryId, both representing the same exact id. I would use country_id in both tables which of course WILL give you the ambiguous error without specifying the table name or alias.

              @tangoforce, you should have went for the trifecta and threw down on both of us. Both queries work.
              Last edited by benanamen; Sep 23, 2016, 11:39 AM.
              To save time, lets just assume I am almost never wrong.

              The XY Problem
              The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

              Make A Donation https://www.paypal.me/KevinRubio

              Comment


              • #8
                Oh yes I know both work.. I was just interested to see which one of you was going to win the arguement lol.

                Credit where it's due, you had the balls to admit you're wrong... not everyone round here can do that.
                "Tango says double quotes with a single ( ' ) quote in the middle"
                '$Name says single quotes with a double ( " ) quote in the middle'
                "Tango says double quotes ( \" ) must escape a double quote"
                '$Name single quotes ( \' ) must escape a single quote'

                Comment


                • #9
                  Originally posted by benanamen View Post
                  DB_name.Country.Country.id
                  You can't insert another level in front of dbname.tablename.fieldname

                  Country.id is already tablename.fieldname so Country.Country.id is dbname.tablename.fieldname so what is that DB_name on the front then supposed to represent as the first Country is the database, the second Country is the table and the id is the field?

                  I had already added the table prefix for that field in my code to make the reference unique so your adding a second prefix would not work unless the database has that name.

                  I think you must have just missed seeing that I had used tablename already on the field that wasn't unique - as an ON clause only ever deals with the two tables on the immediately preceding inner join it is obvious which table the other one must refer to. I'd have included the tablename on that one as well if it wasn't a unique field name.

                  I agree the OPs inconsistent fieldnames would lead to confusion for many.
                  Last edited by felgall; Sep 23, 2016, 07:07 PM.
                  Stephen
                  Learn Modern JavaScript - http://javascriptexample.net/
                  Helping others to solve their computer problem at http://www.felgall.com/

                  Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

                  Comment


                  • #10
                    .... and the scantily clad girl circles the ring, holding up a large sign with a number "3" on it ...

                    Comment


                    • #11
                      I agree the OPs inconsistent fieldnames would lead to confusion for many.
                      That's exactly what got this going. I always use the same names in the keys. I didn't pay attention to OP's naming convention. Response was auto-pilot for how I always do the naming. We were really talking about two different situations.

                      To @Felgal for paying attention to detail in the Op's Post.

                      Click image for larger version

Name:	011.jpg
Views:	1
Size:	45.2 KB
ID:	2270489
                      To save time, lets just assume I am almost never wrong.

                      The XY Problem
                      The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

                      Make A Donation https://www.paypal.me/KevinRubio

                      Comment


                      • #12
                        I have never liked prefixing the ID columns with table name.
                        When you join you already tell the tablename where they come from.
                        You already know which relates to which from the foreign keys.
                        Still the referring column would be TABLENAME_ID without the camel case.

                        With MySQL INNER keyword can be left out. But as the used database was not mentioned then INNER all the way.
                        Last edited by Vege; Sep 24, 2016, 04:55 AM.
                        Don't use old mysql library
                        The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets
                        When a white horse is not a horse

                        Comment


                        • #13
                          One thing to point out is that you cannot use USING when the id's are not the same name.

                          Different ID's
                          INNER JOIN Country ON countryId=Country.id

                          Same name ID's
                          INNER JOIN Country USING (countryId)

                          Also, using using will not return duplicate columns when using SELECT * (You should be selecting by column names specifically)

                          Since same name id's is the coding convention God used when he coded the universe, that's how I do it.

                          Important thing is, pick ONE way and stick to it.
                          Last edited by benanamen; Sep 24, 2016, 12:48 PM.
                          To save time, lets just assume I am almost never wrong.

                          The XY Problem
                          The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

                          Make A Donation https://www.paypal.me/KevinRubio

                          Comment

                          Working...
                          X