How do I connect two tables in SQL to create a new table?

Question

What I am trying to do is add a section the website that automatically assigns a department to a product when an administrator creates a product. For example, if I create a "t-shirt" product then it gets assigned the the "shirts" department. I need to get the tables connected in the db so that when a department is loaded, all the associated products show up. When I run a join between the two tables this is what I am running:

select d.Name, p.Name
       from products p
        inner join departments d on p.Id = d.Id

And I'm getting my only two products which I've manually assigned a department.

Can I get some help with what I need to do next to get every product automatically associated with a department?

How are you manually linking these two tables? Is there any foreign key between these two tables? — Saagar Elias Jacky, Apr 24 '15 at 02:26
How do **you** know that `t-shirt` belongs to `shirts` department? — PM 77-1, Apr 24 '15 at 02:29

score 1 · Accepted Answer · edited Jun 15 '18 at 19:27

1

select d.Name, p.Name
       from products p
        inner join departments d on p.Id = d.Id

you use wrong key to link, (on p.Id = d.Id) try on p.Id= d.foreign_key

edited Jun 15 '18 at 19:27

maxshuty

9,708
13
64
77

answered Apr 24 '15 at 02:37

Shana

70
1
1
8

How do I connect two tables in SQL to create a new table?

1 Answers1