How to Get Two Types of Data in the Same Column

I would like to get data similar to the ideal execution result from the teiki_oyaprod_cd value in the table teiki

ideal execution results meisai is teikimeisai.meisai_no and NULL for set child information

In the prod_cd, if the value of teikiprod_cd or teikiprod_cd of the periodic detail exists in the set_oyaprod_cd of the table set, the value of set child information set_prod_cd of the set detail is also acquired.The sequence of set child information is setdetail's seq ascending order.

Prod_nm is the value of the table prod.prod_name

For prod_price, the value of the table prod.prod_price is NULL for a record of set child information.

Sort by teikimeisai.meisai_no, setdetail.seq
I'd like to get it from .

I didn't know how to get the set parent's product code and the set child's product code in the same column prod_cd in another record, so I got stuck.

table names:prod

table name:teiki

table name:teikimeisai

table names:setprod

table names:setdetail

*Additional: Ideal execution result B

I'm sorry for the unclear point as I don't have much experience in SQL and I've never asked a question before.If you understand, please let me know.
I use postgresql.

Note: Thank you for editing the table layout.

sql postgresql

2022-10-04 01:01

1 Answers

Assume that prod.prod_cd is the "set parent" of the record in teikimeisai.teikiprod_cd.
The usual solution is to use a left external coupling because set parents and set children refer to different tables.
Furthermore, since the set children combine multiple tables, the join statement will use the subqueries together.

Please refer to the sample code below.

sample code

Schema (PostgreSQL v14)

CREATE TABLE PROD(
  "prod_cd" VARCHAR(100),
  "prod_name" VARCHAR(100),
  "prod_price" INTEGER
);

INSERT INTO PROD
  ("prod_cd", "prod_name", "prod_price")
VALUES
  ('wa', 'Product A', '1000'),
  ('wbs', 'Product B Set', '2000'),
  ('wb1', 'Product B1', '1000'),
  ('wb2', 'Product B2', '1000'),
  ('wc', 'Product C', '1000');

CREATE TABLE teiki (
  "teiki_cd" VARCHAR(100),
  "teiki_oyaprod_cd" VARCHAR(100)
);

INSERT INTO teiki
  ("teiki_cd", "teiki_oyaprod_cd")
VALUES
  ('teikia', 'teikioya');

CREATE TABLE teikimeisai (
  "teiki_cd" VARCHAR(100),
  "meisai_no" INTEGER,
  "teikiprod_cd" VARCHAR(100)
);

INSERT INTO teikimeisai
  ("teiki_cd", "meisai_no", "teikiprod_cd")
VALUES
  ('teikia', '1'', 'wa'),
  ('teikia', '2', 'wbs'),
  ('teikia', '3', 'wc');

US>CREATE TABLE "set" (
  "set_oyaprod_cd" VARCHAR(100)
);

INSERT INTO SET
  ("set_oyaprod_cd")
VALUES
  ('wbs');

US>CREATE TABLE setdetail(
  "set_oyaprod_cd" VARCHAR(100),
  "seq" INTEGER,
  "sed_prod_cd" VARCHAR(100)
);

INSERT INTO set detail
  ("set_oyaprod_cd", "seq", "sed_prod_cd")
VALUES
  ('wbs', '1', 'wb1'),
  ('wbs', '2', 'wb2');

Query #1

selectm.meisai_no meisai,
       p.prod_cd,
       p.prod_name prod_nm,
       case when ssd.set_oyaprod_cd is null
            thenp.prod_price
            else null
       end prod_price
from prodp
       left join teikimeisaitm
       on p.prod_cd = tm.teikiprod_cd
       left join (select tm2.meisai_no,
                         s.set_oyaprod_cd,
                         sd.seq,
                         sd.sed_prod_cd
                  from "set"s
                         join teikimeisaitm2
                         ons.set_oyaprod_cd=tm2.teikiprod_cd
                         join setdetailsd
                         ons.set_oyaprod_cd = sd.set_oyaprod_cd) as ssd
       on p.prod_cd = ssd.sed_prod_cd
order by coalesce (tm.meisai_no, ssd.meisai_no),
         ssd.seq nulls first;

View on DB Fiddle

Notes

I didn't use it because I couldn't read the need for teiki_oyaprod_cd in the table teiki.
The prod table wc records have a prod_price of 1000, which is different from the ideal execution result of 1500.
This table is also known as set, but it is more readable and serviceable not to use the words in the SQL statement itself, such as set.

Welcome to Stack Overflow!
I would appreciate it if you could write down the specifications in detail, but if you focus on the points that are stuck or the traces of trial and error, it will be easier to get the right answer.
*Questions with only specifications and desired movements give the impression that they are commonly called "round throw."

Even if SQL statements do not work, it would be better to post comments such as --I don't understand in SQL comments, so please be aware of the inappropriate answers or future questions.

2022-10-04 01:01

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656