easy inner join

[miffe]

Let's say that the following two tables exist:

Table: users-----------------idUseruserNamepassword-Table: orders-----------------idOrderidProductidUser

This is a really simple question but I've been having trouble with it... I just want to know the correct code to show in a result query the following when SQL is queried to show the products in the order que (ordered by idOrder, userName) :

orders.idOrderusers.userNameorders.idProductorders.idUser

thanks a lot,miffe

[jesh]

SELECT orders.idOrder, orders.idProduct, orders.idUser, users.userNameFROM orders INNER JOIN users ON orders.idUser = users.idUser

[miffe]

SELECT orders.idOrder, orders.idProduct, orders.idUser, users.userNameFROM orders INNER JOIN users ON orders.idUser = users.idUser

thanks a lot, exactly what I needed ^^

[miffe]

Ok this is the code to join one table's values with a value from another table right...

SELECT table1.col1, table1.col2, table2.col1FROM table1 INNER JOIN table2 ON table1.id = table2.id

But... lets say we want to join one table with values from a second AND a third table...how do I perform multiple inner joins without creating messy code such as M$ access produces? I mean, for the actual knowledge of writing correct various inner joins, I'd just like to know so I can do this without any help in the future.Thanks - miffe

[packrat]

practive miffe, practice. I think it took me about a year to really get my head around all the various joins.I've noticed that the query editors tend to put the tables in some odd arrangements, I wouldn't pay too much attention to the way that the automated tools build joins. I tend to put them in the logical order of my query; if i'm querying order info my first table would be orders then order details, then the products table if need be. The optimizer is pretty good at figuring out what it wants to do.My other recomendation while you learning joins is to make extensive use of sub queries. In most cases a subquery can be collapsed into a join condition but it can save your behind of you're floundering over a join. At the very least the subquery helps segment the logic so you can keep track of what the parts are doing, you can more easily test the components that way. I'd say that more than half the time my all join queries run about the same speed as my subquery queries. Again the optimizer is pretty good at figuring out what it wants to do.

[jesh]

But... lets say we want to join one table with values from a second AND a third table...how do I perform multiple inner joins without creating messy code such as M$ access produces? I mean, for the actual knowledge of writing correct various inner joins, I'd just like to know so I can do this without any help in the future.

Maybe an example would help. Suppose, based on your original post, you had the following:

Table: users-----------------idUseruserNamepasswordTable: products-----------------idProductdescriptionTable: orders-----------------idOrderidProductidUser

A query to get the username of the user and the description of the product for each order would look like this:

SELECT users.userName, products.descriptionFROM ordersINNER JOIN users ON orders.idUser = users.idUserINNER JOIN products ON orders.idProduct = products.idProduct

It all really depends on how each of the tables relates to one another.Also, when JOINing tables, it might help to look into using LEFT JOINs and RIGHT JOINs if necessary. The W3Schools page on SQL JOINs helped me when I was first learning: http://www.w3schools.com/sql/sql_join.asp

[miffe]

Madness!Thanks a lot you two! I was trying first inner joins with (parenthesis)'s but unsuccesfully somehow it was leaving out some values, like for different categories and stuff... I made a query with 12 inner joins so it was getting quite messy with all the parenthesis for each inner join... something like this: (((((INNER JOIN...)INNER JOIN...)INNER JOIN...).... but let me try the left and right inner joins...Another question: What is OUTER JOIN ? I think Ive heard of it before but got no clue.I'll keep on practicing meanwhile :)Thanks -Miffe

Maybe an example would help. Suppose, based on your original post, you had the following:A query to get the username of the user and the description of the product for each order would look like this:

SELECT users.userName, products.descriptionFROM ordersINNER JOIN users ON orders.idUser = users.idUserINNER JOIN products ON orders.idProduct = products.idProduct

It all really depends on how each of the tables relates to one another.Also, when JOINing tables, it might help to look into using LEFT JOINs and RIGHT JOINs if necessary. The W3Schools page on SQL JOINs helped me when I was first learning: http://www.w3schools.com/sql/sql_join.asp

[packrat]

the syntax gets a little squirrely around outer, its more confusing than it needs to be.OUTER JOIN only comes into play when you're doing a LEFT or RIGHT join.LEFT JOIN is the same as LEFT OUTER JOINRIGHT JOIN is the same as RIGHT OUTER JOINWhereas an INNER JOIN only return results from either table when the specified key(s) match the OUTER JOIN returns all the rows from the LEFT or RIGHT in the ON portion of the join.i.e: Table1 LEFT OUTER JOIN Table2 ON Table1.key = Table2.keythis returns ALL rows from Table1 and fills all the Table2 results with NULLs where the key does not match.RIGHT simply reverses this so that:i.e: Table1 RIGHT OUTER JOIN Table2 ON Table1.key = Table2.keyreturns ALL rows from Table2 and only the matching keys from Table1, filling the unmatched rows with NULLs.You could decide to always write LEFT or RIGHT and you wouldn't be out anything.the significance is that it returns all the rows from the table specified returning NULLs where there is no match to its key.It doesn't help understanding that in most flavors of SQL the OUTER part is optional. This is one of those things that is actually simpler than it seems.

[miffe]

the syntax gets a little squirrely around outer, its more confusing than it needs to be.OUTER JOIN only comes into play when you're doing a LEFT or RIGHT join.LEFT JOIN is the same as LEFT OUTER JOINRIGHT JOIN is the same as RIGHT OUTER JOINWhereas an INNER JOIN only return results from either table when the specified key(s) match the OUTER JOIN returns all the rows from the LEFT or RIGHT in the ON portion of the join.i.e: Table1 LEFT OUTER JOIN Table2 ON Table1.key = Table2.keythis returns ALL rows from Table1 and fills all the Table2 results with NULLs where the key does not match.RIGHT simply reverses this so that:i.e: Table1 RIGHT OUTER JOIN Table2 ON Table1.key = Table2.keyreturns ALL rows from Table2 and only the matching keys from Table1, filling the unmatched rows with NULLs.You could decide to always write LEFT or RIGHT and you wouldn't be out anything.the significance is that it returns all the rows from the table specified returning NULLs where there is no match to its key.It doesn't help understanding that in most flavors of SQL the OUTER part is optional. This is one of those things that is actually simpler than it seems.

excellent, thanks, I got the hang of that now...Actually I've got one more problem I'd like to discuss... this involves a bit of a more difficult join, lets say I have a table which links to other tables directly with different id numbers, but one of those id's links to another table which is a 'many-to-many' relationship table that links to another one... and I want to display data from the first table and from the third.Ive found it quite difficult... lets say I have the following:

modelos-------idModelonombreidProcesoidCategoria.procesos--------idProcesonombreProceso.categorias----------idCategorianombreCategoria.coloresModelos--------------idModeloidColor.colores-------idColornombreColor

Basically I want to show the info from modelos, and inner join to the nombreProceso and nombreCategoria and at the same time show me all the nombreColor from the relationships in coloresModelos...Final output should be something like:idModelo | nombre | nombreProceso | nombreCategoria | colorin case there was no color specified in the relationship it just wouldnt come up, thats easy... with a left join... but I just cant get the hang of showing the colors :\I did one once with access and it was easy, but I have Access produces SQL and right now im coding all myself in php - mysql for this system..Any help?Thanks - Miffe

[jesh]

Does this do what you need?

SELECT m.idModelo, m.nombre, p.nombreProceso, ca.nombreCategoria, co.nombreColor AS colorFROM modelos m    INNER JOIN procesos p ON m.idProceso = p.idProceso    INNER JOIN categorias ca ON m.idCategoria = ca.idCategoria    INNER JOIN coloresModelos cm ON m.idModelo = cm.idModelo    INNER JOIN colores co ON cm.idColor = co.idColor

[miffe]

Does this do what you need?

SELECT m.idModelo, m.nombre, p.nombreProceso, ca.nombreCategoria, co.nombreColor AS colorFROM modelos m    INNER JOIN procesos p ON m.idProceso = p.idProceso    INNER JOIN categorias ca ON m.idCategoria = ca.idCategoria    INNER JOIN coloresModelos cm ON m.idModelo = cm.idModelo    INNER JOIN colores co ON cm.idColor = co.idColor

Excellent... thanks again Jesh!I was trying something very similar but for some reason it wasnt working for me, it strange just how a small symbol or character can mess everything up and take ages for one to notice it -miffe