CODEWITHSUNDEEP
sql
David
David

Reputation: 762

How to create these SQL tables

I have a table for 'Parts':

so an example Parts Table:

I want to make 'groups' of these parts so for example a 'normal' group would be a book and table where an 'extra' group would be a book and desk.

You'd be able to do "SELECT * FROM 'groups' WHERE name='normal'". How would I accomplish this?

Hopefully I explained this good enough (Google searches for 'groups' fails due to the SQL group by keyword)

Upvotes: 2

Views: 147

Answers (1)

Keith Irwin
Keith Irwin

Reputation: 5668

You need a second table which defines the groups and which items belong to it.

So, in this case, you'd want a table named "Groups" with attributes

  • GroupName (PK)
  • ItemID (PK) (Foreign Key from Parts)

(The Foreign Key constraint listed above is not strictly necessary, but the primary key does definitely have to be both GroupName and ItemID. If it is just one or the other, the table will not work right.)

And then the entries would be

  • "normal" 1
  • "normal" 3
  • "extra" 1
  • "extra" 2

Then you would want to use a query like

SELECT * FROM Groups, Parts WHERE Groups.ItemID = Parts.ID

or

SELECT * FROM (Groups JOIN Parts ON Groups.ItemID = Parts.ID)

Upvotes: 2

Related Questions