Peter Penzov Peter Penzov - 6 months ago 12
SQL Question

Internal SQL query

I want to get PAYMENT_GATEWAY name with SQL query. I use this tables:

CREATE TABLE PAYMENT_GATEWAY(
ID SERIAL NOT NULL,
NAME TEXT
)
;

ALTER TABLE PAYMENT_GATEWAY ADD CONSTRAINT KEY38 PRIMARY KEY (ID)
;

CREATE TABLE PAYMENT_GATEWAY_ACCOUNT(
ID SERIAL NOT NULL,
PAYMENT_GATEWAYT_ID INTEGER
)
;

CREATE INDEX IX_RELATIONSHIP18 ON PAYMENT_GATEWAY_ACCOUNT (PAYMENT_GATEWAYT_ID)
;

ALTER TABLE PAYMENT_GATEWAY_ACCOUNT ADD CONSTRAINT KEY39 PRIMARY KEY (ID)
;


select query

SELECT * FROM PAYMENT_GATEWAY_ACCOUNT


As you can see I store PAYMENT_GATEWAYT_ID as number. How I can modify the SQL query to select column NAME into PAYMENT_GATEWAY and display name String as result?

Answer

From what I can tell, you asking how to join these two tables together - clarify is this is not correct.

SELECT
    pga.ID,
    pg.NAME
FROM
    PAYMENT_GATEWAY_ACCOUNT pga
INNER JOIN
    PAYMENT_GATEWAY pg
    ON (pg.ID = pga.PAYMENT_GATEWAYT_ID);

The above will get you the NAME that is associated with the ID in PAYMENT_GATEWAY_ACCOUNT

Another thing to note is that you should consider adding a FOREIGN KEY reference to PAYMENT_GATEWAY_ACCOUNT to maintain referential integrity.

CREATE TABLE PAYMENT_GATEWAY_ACCOUNT(
    ID SERIAL NOT NULL,
    PAYMENT_GATEWAYT_ID INTEGER REFERENCES PAYMENT_GATEWAY(ID)
);

From the docs: https://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-FK