CODEWITHSUNDEEP
sqldatabasejoin
hakubaa
hakubaa

Reputation: 169

Join data from two tables without unique key

I have two tables in one database (access file).

Table 1:

| Product Id |  Year     | Sales |
----------------------------------
| 1144       |  2013     | 100   |
| 20131120   |  2013     | 200   |
| 1144       |  2012     | 333   |

Table 2:

| Product Id     |  Category     |
----------------------------------
| 1144           | Car           |
| 20131120       | Motorbike     |

The first table (table 1) is the table when user can add new data whenever wants, and the second table is updated once in a while, when new product id appears. I would like to join this table, some kind of relation on the base of Product Id, but I'm not able to use relation due to the lack of unique, primary key, am I right? When I execute SQL query ( SELECT * FROM Table 1) I would like to get the result like this one:

| Product Id |  Year     | Sales | Category   |
-----------------------------------------------
| 1144       |  2013     | 100   | Car        |
| 20131120   |  2013     | 200   | Motrobike  |
| 1144       |  2012     | 333   | Car        |

I know I can join this two tables, but I would like not to do it every time when user add new data to table 1. Do you know any solution how could I do that?

Upvotes: 1

Views: 10593

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

I'm not able to use relation due to the lack of unique, primery key, am I right?

No, this is not right, you can normally JOIN the two tables. It’s also legal to JOIN two tables or result sets on any columns that have what so called eligible data types.

SELECT
  t1.ProductId, t1.year, t1.sales, t2.category
FROM table1 AS t1
INNER JOIN table2 AS t2 ON t1.productId = t2.productId;

but I would like not to do it every time when user add new data to table 1

You don't need a ProductId in the second table, you need a CategoryId instead, and add a new column CategoryId to the first table too and declare it as a foreign key. Your tables should look like so:

Table1 (Products):

  • ProductId,
  • CategoryId,
  • Year,
  • Sales.

Table2 (Categories):

  • CategoryId,
  • CategoryName.

Then the category is added once to the categories table table2 and the product is added once to the products table table1. And for each product enter a CategoryId for it. This what so called normalization

You should also define ProductId in table1 and CategoryID in table2 as primary keys, and CategoryID in the first table as foreign key.

Upvotes: 3

Related Questions