I have 2 separate querie开发者_如何学运维s that returns to me the following tables:
===========================
Id f_name l_name
===========================
15 Little Timmy
16 John Doe
17 Baby Jessica
---------------------------
===========================
Id item_name item_price
===========================
15 Camera 100
15 Computer 200
16 Pony 55
---------------------------
In MySQL, how do I combine the 2 into this:
===================================================
Id f_name l_name item_name item_price
===================================================
15 Little Timmy Camera 100
15 Little Timmy Computer 200
16 John Doe Pony 55
17 Baby Jessica
---------------------------------------------------
Any assistance is greatly appreciated. Thanks.
select
name_table.Id,
name_table.f_name,
name_table.l_name,
item_table.item_name,
item_table.item_price
from
name_table
left join item_table
on name_table.Id = item_table.Id
Enjoy!
There are a lot of types of Joins, and it's a very interesting and important topic in database management. SO's own Jeff Atwood has a very good post describing the different types of joins and cases in which they would be used.
You need to use a left outer join:
SELECT names.Id, names.f_name, names.l_name, prices.item_name, prices.item_price
FROM names
LEFT OUTER JOIN prices
ON names.Id = prices.Id
If you want to perform this select statement regularly in your application, you might want to consider creating a view in the database. A view is basically a combination of data in more than one table. A major benefit of using a view here would be a simpler query in your app, so instead of doing the join in the app, you would simply select all the fields from the view.
Your CREATE VIEW statement would look more or less like Doug's answer, whilst your select would then be
select ID, f_name, l_name, item_name, item_price from my_view;
More details on MySQL CREATE VIEW
And if you want to to create a table with that result in database, do any of these selects/joins in a create table command.
CREATE TABLE foobar SELECT ...
精彩评论