CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.3postgresql-9.4
Peter Penzov
Peter Penzov

Reputation: 1680

Select data with Join

I want to select rows from the table with the same ID.

   CREATE TABLE ACCOUNT(
 ID INTEGER NOT NULL,
 USER_NAME TEXT NOT NULL,
 PASSWD TEXT,
 FIRST_NAME TEXT,
 LAST_NAME TEXT,
 E_MAIL TEXT NOT NULL,
 COUNTRY TEXT,
 STATE TEXT,
 CITY TEXT,
 ADDRESS TEXT,
 STATUS INTEGER,
 SECURITY_QUESTION TEXT,
 SECURITY_ANSWER TEXT,
 LAST_PASSWD_RESET DATE,
 DESCRIPTION TEXT,
 LAST_UPDATED DATE,
 CREATED DATE
)
;

-- ADD KEYS FOR TABLE ACCOUNT

ALTER TABLE ACCOUNT ADD CONSTRAINT KEY1 PRIMARY KEY (ID)
;

ALTER TABLE ACCOUNT ADD CONSTRAINT USER_NAME UNIQUE (USER_NAME)
;

ALTER TABLE ACCOUNT ADD CONSTRAINT E_MAIL UNIQUE (E_MAIL)
;

-- TABLE ACCOUNT_ROLE

CREATE TABLE ACCOUNT_ROLE(
 ID INTEGER NOT NULL,
 USER_NAME TEXT NOT NULL,
 ROLE INTEGER,
 PERMISSION TEXT,
 LAST_UPDATED DATE,
 CREATED DATE
)
;

-- CREATE INDEXES FOR TABLE ACCOUNT_ROLE

CREATE INDEX IX_RELATIONSHIP19 ON ACCOUNT_ROLE (ID)
;

-- ADD KEYS FOR TABLE ACCOUNT_ROLE

ALTER TABLE ACCOUNT_ROLE ADD CONSTRAINT KEY26 PRIMARY KEY (ID)
;

ALTER TABLE ACCOUNT_ROLE ADD CONSTRAINT RELATIONSHIP19 FOREIGN KEY (ID) REFERENCES ACCOUNT (ID) ON DELETE CASCADE ON UPDATE CASCADE
;

What join query I need to use in order to get the result with proper ID? As you can see I use ID as primary key in both tables with cascade delete.

Upvotes: 0

Views: 98

Answers (2)

Mikhail Chibel
Mikhail Chibel

Reputation: 1955

SELECT * FROM ACCOUNT_ROLE INNER JOIN ACCOUNT ON ACCOUNT.ID=ACCOUNT_ROLE.ID

By the way, don't save password as a clear text in the database. Use some sort of hashing. Also, having both tables linked by primary keys is not a good design.

Upvotes: 2

Ash
Ash

Reputation: 6035

It looks like you just want a simple join

select A.*,R.* from Account A inner join Account_Role R on 
A.Id = R.Id

You can select the columns you need by modifying the above query as follows:

select A.ID A.USER_NAME, A.PASSWD TEXT, A.FIRST_NAME TEXT, --and so on
R.Id, R.User_name, R.Role_Integer --and so on 
from Account A inner join Account_Role R on 
A.Id = R.Id

Upvotes: 2

Related Questions