CODEWITHSUNDEEP
sqloracle-databaseselect
The_Bear
The_Bear

Reputation: 173

Select everything from one table, and certain columns from another SQL

How can I select everything from one table and certain columns from another when creating a view,

For example I've wrote

CREATE VIEW NOT_IN_MAN_GLA_LND
AS 
     SELECT 
         E.EMPLOYEE_ID, E.TITLE, E.FIRST_NAME, E.LAST_NAME, 
         E.HOUSE_NO, E.ADDRESS, E.POSTCODE, E.TELE_NO, E.START_DATE, 
         P.CITY, P.POST_CODE
     FROM 
         EMPLOYEE E, POSTCODE P
     WHERE 
         E.POSTCODE = P.POSTCODE
         AND P.CITY NOT IN ('MAN', 'GLA', 'LND');

So instead of writing all the column from the employee table is there anyway I can just select them all while still selecting only the two from the postcode table.

Upvotes: 1

Views: 5513

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

The better answer is to use proper, explicit, STANDARD JOIN syntax:

CREATE VIEW NOT_IN_MAN_GLA_LND AS
    SELECT E.*, P.CITY, P.POST_CODE
    FROM EMPLOYEE E JOIN
         POSTCODE P
         ON E.POSTCODE = P.POSTCODE
    WHERE P.CITY NOT IN ('MAN', 'GLA', 'LND');

If you wanted to include employees in the results -- even those who have no matching postal code -- then:

CREATE VIEW NOT_IN_MAN_GLA_LND AS
    SELECT E.*, P.CITY, P.POST_CODE
    FROM EMPLOYEE E LEFT JOIN
         POSTCODE P
         ON E.POSTCODE = P.POSTCODE
    WHERE P.CITY NOT IN ('MAN', 'GLA', 'LND') OR
          P.CITY IS NULL;

Upvotes: 2

Nikhil
Nikhil

Reputation: 3950

create or replace VIEW NOT_IN_MAN_GLA_LND
AS SELECT E.*, P.CITY, P.POST_CODE as P_POST_CODE

FROM EMPLOYEE E, POSTCODE P

WHERE E.POSTCODE = P.POSTCODE

AND P.CITY NOT IN ('MAN', 'GLA', 'LND');

for example dept nd emp table:

create or replace view ns_1_v as
select e.*,d.deptno as d_dname,d.dname 
from scott.emp e ,scott.dept d
where
e.deptno=d.deptno;

you have to give aliases for the column names where there is a common name in both the tables otherwise it will throwdupllicaate column name error in your case postcode is common so give alias for it

Upvotes: 0

Related Questions