SQL to combine 2 tables

Question

I have 2 separate queries 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.

Answer 1

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

Answer 2

Hi,

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!

Answer 3

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 ...

Answer 4

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

Answer 5

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.