Online computer courses, code, programming tutorial and sidebar information for monitoring Canadian S&P/TSX index. Build friendship and networking. Welcome to visit my blogs often!!! I also have two other sites: YouTube Channel and Google site.
Adsense
Popular Posts
- PHPWind-- A PHP forum script applcaition in China
- Free host forum is not reliable
- Formatting my post
- Powerful js library datable to export table in pdf, excel, sorting, freezing column and table header fixed (sticky)
- job interview questions (1)
- Set up a child account and set screen time limit in Windows 8
- PHP: add a download as pdf file button in report page
- phpexcel toggle expand and hide column in EXCEL and summary
- WinMerge - file and folder differencing and merging tool for Windows
- PHP connect IBM db2 database in XAMPP
Tuesday, August 30, 2011
Select from multiple tables in MySQL
Below is the example using full join and left join in MySQL.
Suppose we have two tables: members and committee.
table members (primary key member_id)
--------------------------------------
member_id member_name
1 Joe
2 Andy
3 Tommy
table committee (primary key committee_id)
----------------------------------------------
committee_id member_id committee_name
1 2 Research
2 2 Education
3 3 Research
Full join
SELECT members.*, committee.* FROM members, committee;
Result:
------------------------------------------------------------------------------------------------------
member_id member_name committee_id member_id committee_name
1 Joe 1 2 Research
2 Andy 1 2 Research
3 Tommy 1 2 Research
1 Joe 2 2 Education
2 Andy 2 2 Education
3 Tommy 2 2 Education
1 Joe 3 3 Research
2 Andy 3 3 Research
3 Tommy 3 3 Research
SELECT members.*, committee.* FROM members, committee WHERE members.committee_id=committee.committee_id;
Result:
------------------------------------------------------------------------------------------------------
member_id member_name committee_id member_id committee_name
2 Andy 1 2 Research
2 Andy 2 2 Education
3 Tommy 3 3 Research
Left join
SELECT members.*, committee.* from members LEFT JOIN committee ON members.committee_id=committee.committee_id;
Result:
(A left join produces output for every row in members, whether or not committee matches it. )
------------------------------------------------------------------------------------------------------
member_id member_name committee_id member_id committee_name
1 Joe NULL NULL NULL
2 Andy 1 2 Research
2 Andy 2 2 Education
3 Tommy 3 3 Research
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment