CODEWITHSUNDEEP
mysqlphpmyadmin
ritwikshanker
ritwikshanker

Reputation: 477

How to create a table where one column contains a same value for all rows with different id?(See Layout for clarity)

Image showing example report, empty

In this table score, start date , Completion Date will be same for all error code but will be taken as an input once in the beginning.

Upvotes: 0

Views: 197

Answers (1)

O. Jones
O. Jones

Reputation: 108736

This is a guess because your question isn't very clear. You are looking for a one-to-many pattern to hold this collection of data.

It looks like you have two entities (in the parlance of entity-relationship data modeling).

  1. Review
  2. Review Error

Review has a one-to-many relationship to Review Error: each review may have zero or more Review Errors associated with it. You showed two in your image, but you should still use the one-to-many relationship.

Your Review entity turns into a table like this

review:
   review_id    INT   PK
   type         1=Peer Review, 2= Internal Review
   score        FLOAT?  INT?
   start_date   TIMESTAMP
   end_date     TIMESTAMP

Your Review Error entity turns into a table like this

review_error_id    INT PK
review_id          INT FK to review table
ordinal            INT   0,1,2 showing order of items in report
error              VARCHAR(255)  
description        VARCHAR(255)
quantity           INT? FLOAT?

Then to generate the report hinted at by your example table, you do

 SELECT review.type,
        review_error.error,
        review_error.description,
        review_error.quantity
        review.score,
        review.start_date,
        review.end_date
   FROM review
   LEFT JOIN review_error ON review.review_id = review_error.review_id
  ORDER BY review.review_id
           review_error.ordinal

You use LEFT JOIN because ordinary JOIN will suppress review rows that have no matching review_error rows.

If you want the boxes and column headers shown in your image, you'll need to generate those with a client side program. For example, you can define borders around cells in HTML tables.

Upvotes: 1

Related Questions