CODEWITHSUNDEEP
sqlrelational-database
Bleepbloop
Bleepbloop

Reputation: 11

Have a table in SQL where one column can have multiple values

I am trying to create a table in NetBeans using SQL code and was wondering how you assign multiple items to one column. I have tried using NULL in my insert statement and that doesn't work.

An example of what I mean is having a Retailer only be listed once in the first column but have 3 values in the next column as Items associated to the retailer.

Upvotes: 1

Views: 1454

Answers (2)

Joseph M
Joseph M

Reputation: 84

If i were you, I would add an id column for each table, which would then allow for a join to be made. For this to work, you would need to edit the existing tables to allow foreign key joins to a new table, say 'products sold', that will link the two other tables together. The amount of sales could then be counted using the 'count' command.

Upvotes: 1

Nate Barbettini
Nate Barbettini

Reputation: 53640

Based on your description:

  • The database has 2 retailers: InfoWare and SoftwareLand
  • Each retailer has one or more items
  • An item tracks how many times it has been sold

... building a database with a single table and multiple columns is not the correct solution. A common relational database pattern would be to have a Retailers table:

[Retailers table]
int RetailerID (primary key)
string Name

And an Items table:

[Items table]
int ItemID (primary key)
int RetailerID (foreign key)
string Name
int QuantitySold

Each item "belongs" to a retailer because of the foreign key relationship. This is a much better design than having a column per item - that's a bad anti-pattern of relational databases.

Upvotes: 3

Related Questions