Skip to main content
A
Anonymous
January 15, 2012
Answered

Help! How Do I Use Inner Join?

  • January 15, 2012
  • 3 replies
  • 732 views

I love using Coldfusion, but my coding knowlege is basic. I just don't understand inner join coding yet. Using the below code, I can get Column1 (barcode) and Column2 (quantity). How can I use inner join to get Columns with product_name and price from another table (inventory - based on same barcode number in both tables) so that I can insert ALL of the four fields into a new table (cart1)

<!--- FIND DUPLICATES AND SUM QUANTITY --->

<cfquery name="CountBarcodesTemp" datasource='inventory'>

SELECT barcode, sum(quantity) as quantitysum

FROM temp 

GROUP BY barcode

</cfquery>

- Thanks.

    This topic has been closed for replies.
    Correct answer Adam Cameron.

    OK, one first thing to consider here is that this is a CF forum, and you're asking an SQL question.  This really ain't be best place to be directing SQL questions (that's not to say you won't get an answer, but it's not really appropriate for these forums).

    There's not a great deal to INNER JOIN usage.  It's just this:

    SELECT [your columns here]

    FROM table1 INNER JOIN table2

    ON table1.someCol = table2.someOtherCol

    Where someCol and someOtherCol represent a relationship between the two tables; in your case it seems that the barcode ID is the column that relates the two tables, so the join statement would be along the lines of

    FROM temp INNER JOIN inventory

    ON temp.barcode = inventory.barcode

    That said, it makes for more readable SQL if you alias your tables too, eg:

    SELECT t.somerCol, i.someOtherCol

    FROM temp t INNER JOIN inventory i

    ON i.barcode = i.barcode

    Wherever you wish to reference the tables thereafter, you use the alias, not the table name.

    I think you should probably seek out the online docs for whichever DB you're using and give them a read.  Or buy a SQL tutorial book and work through it, or something, too.

    --

    Adam

    3 replies

    R
    rb012
    Participant
    January 16, 2012

    SQL INNER JOIN may be defined as

    The INNER JOIN in SQL joins two tables according to the matching of a certain criteria using a comparison operator.

    You may refer this SQL INNER JOIN doc.

    D
    Dan_Bracuk
    Inspiring
    January 15, 2012

    I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.

    A
    Adam Cameron.Correct answer
    Inspiring
    January 15, 2012

    OK, one first thing to consider here is that this is a CF forum, and you're asking an SQL question.  This really ain't be best place to be directing SQL questions (that's not to say you won't get an answer, but it's not really appropriate for these forums).

    There's not a great deal to INNER JOIN usage.  It's just this:

    SELECT [your columns here]

    FROM table1 INNER JOIN table2

    ON table1.someCol = table2.someOtherCol

    Where someCol and someOtherCol represent a relationship between the two tables; in your case it seems that the barcode ID is the column that relates the two tables, so the join statement would be along the lines of

    FROM temp INNER JOIN inventory

    ON temp.barcode = inventory.barcode

    That said, it makes for more readable SQL if you alias your tables too, eg:

    SELECT t.somerCol, i.someOtherCol

    FROM temp t INNER JOIN inventory i

    ON i.barcode = i.barcode

    Wherever you wish to reference the tables thereafter, you use the alias, not the table name.

    I think you should probably seek out the online docs for whichever DB you're using and give them a read.  Or buy a SQL tutorial book and work through it, or something, too.

    --

    Adam

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices