CODEWITHSUNDEEP
postgresql
Josh
Josh

Reputation: 1

How to Carry over a Table into a Column PostgreSQL

This May be a dumb question as I am a beginner in postgreSQL but what I'm trying to do is

I have a Table called Products and inside products there is 3 columns Name, Price, Expiry Date. Now I have a second table called orders with 4 columns. Product, purchasePrice, Amount, and CountryRecieved.

All I want is to reference the Product column to the product table so it has all the Information of the product table?

Is this do able?

Upvotes: 0

Views: 33

Answers (2)

IMSoP
IMSoP

Reputation: 97977

The key concepts you need to read up on are:

  • "normalisation": the process of breaking down data into multiple related entities
  • "foreign keys": pointers from one database table to another
  • "joins": the query construct used to follow that pointer and get the data back together

In your case:

  • You have correctly determined that the information from Products should not just be copied manually into each row of the Orders table. This is one of the most basic aspects of normalisation: each piece of data is in one place, so updates cannot make it inconsistent.
  • You have deduced that the Orders table needs some kind of Product column; this is your foreign key. The most common way to represent this is to give the Products table an ID column that uniquely identifies each row, and then have a ProductID column in the Orders table. You could also use the product's name as the key, but this means you can never rename a product, as other entities in the database might reference it; integer keys will generally be more efficient in storage and speed, as well.
  • To use that foreign key relationship, you use a JOIN in your SQL queries. For example, to get the name and quantity of products ordered, you could write:
SELECT 
    P.Name, 
    O.Amount 
FROM 
    Products as P
INNER JOIN
    Orders as O
    -- This "ON" clause tells the database how to look up the foreign key
    On O.ProductId = P.ProductId
ORDER BY
    P.Name

Here I've used an "inner join"; there are also "left outer join" and "right outer join", which can be used when only some rows on one side will meet the condition. I recommend you find a tutorial that explains them better than I can in a single paragraph.

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39527

Assuming the name column is key in Products table and product column in Orders table refers to it, you can join the two table on related column(s) and get all the information:

select
    o.*, p.*
from orders o
join products p on o.product = p.name;

Upvotes: 0

Related Questions