Link to home
Create AccountLog in
Avatar of numberkruncher
numberkruncherFlag for United Kingdom of Great Britain and Northern Ireland

asked on

MySQL Query - Join?

I need to query my database and somehow join two tables:
table1:
=======
id   name
-----------------
1    bob
2    jessica

table2:
=======
id   table1_id   colour
-----------------------------
1    1           red
2    1           green
3    2           blue
4    1           yellow
5    NULL        fuchsia

Required Result:
================
id   name        colour
-----------------------------
1    bob         red
2    bob         green
3    jessica     blue
4    bob         yellow
5    NULL        fuchsia

Open in new window

Avatar of antonybrahin
antonybrahin

this should work..

select table2.id, table1.name, table2.colour
from table1 inner join table2 on table1.id = table2.table1_id
ASKER CERTIFIED SOLUTION
Avatar of ashokadi
ashokadi

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of numberkruncher

ASKER

What is the difference between an inner join and a left join?
@ashokadi: you are correct. use left join.

There is no difference actually.

select table2.id, table1.name, table2.colour
from table2 inner join table1 on table1.id = table2.table1_id

the above query is equal to the below one

select t2.id,t1.name,t2.colour from table2 as t2 left join table1 as t1 on t2.table1_id=t1.id
This works with the exception that the NULL fields are not shown. How can I get those to appear?
Is it:
SELECT t2.id,t1.name,t2.colour FROM table2 as t2 LEFT JOIN table1 as t1 ON t2.table1_id=t1.id OR t2.table1_id IS NULL

Open in new window

Hi,

Of course there's a HUGE difference between INNER and LEFT (or any OUTER JOIN)

Table1 - Users

ID -  Name
1     John
2     Peter
3     Brian
4     Mary
5     Leonor


Table2 - Departments
ID   Name
1     Accounting
2     IT
3     Sales


Table3  - UserDepartments
ID    UserID    DepartmentID
1        1                 1
2        2                 1
3        3                 2


If you do SELECT  Users  INNER JOIN UserDepartments YOU WILL NOT GET

4     Mary
5     Leonor

--------------
ID -  Name  ID    UserID  DepartmentID
1     John     1       1                1
2     Peter    2       2                1
3     Brian    3       3                2

-------------


if you do SELECT  Users  LEFT JOIN UserDepartments YOU WILL GET ALL USERS, but for rows

4     Mary
5     Leonor

you will not get info for columns in UsersDepartments

---------------------
ID -  Name      ID       UserID  DepartmentID
1     John         1           1                1
2     Peter        2           2                1
3     Brian         3           3                2
4     Mary     NULL   NULL         NULL
5     Leonor   NULL   NULL         NULL
--------------------



So, there's a big difference, you can google it if you want more details.


Hope it helps.


Cheers.
hi,
If you use the above query
SELECT t2.id,t1.name,t2.colour FROM table2 as t2 LEFT JOIN table1 as t1 ON t2.table1_id=t1.id OR t2.table1_id IS NULL

It will result your expected values. if you use is null , it will give only 5th id in your table.

Looking at the required result in the original post, do you want the "id" to be a sequential number or the same as the "id" from table2?  In other words, the output you want is just the contents of table2, but with the name from table1 inserted in place of the table1_id column?
The "Required Result" id must be the same as "table2"

"table2" is essentially to be left untouched, with the exception that the "name" field is added wherever possible. It might be easier to keep the "table1_id", but add "name"...
Avatar of Guy Hengel [angelIII / a3]
what is wrong with comment 34462681 from ashokadi ?
select t2.id,t1.name,t2.colour from table2 as t2 left join table1 as t1 on t2.table1_id=t1.id 

Open in new window


note that the "order" of the tables, when doing left join, is important.
his query shall return exactly what you need.
@angelIII: It looks fine to me.  I was asking to clarify whether those were to be sequential line numbers or ID numbers.  Happy New Year to all, ~Ray
Thanks guys! Does exactly what I need!