CODEWITHSUNDEEP
sqloracle-database
rel0aded0ne
rel0aded0ne

Reputation: 461

SQL - SELECT statement over two tables

I want to create a SELECT statement which selects two different columns from two tables in the same database. 

SELECT  TNC301X.DIRIN, NC301B.PATH301
FROM    TNC301X, NC301B
WHERE   TNC301X.EDIPROC like 'P30_' AND NC301B.EDIPROC like 'P30_'
    AND (LASTENRIN > 0) OR (LF301M > 0) 
    AND (DIRIN NOT LIKE '%saptemp%')
    AND (DIRIN NOT LIKE '%SAPTEMP%')  
    AND (DIRIN NOT LIKE '%usr%')  
    AND (DIRIN NOT LIKE '%Windows%');

I just want to select the content in the columns DIRIN and PATH301.

TNC301X.DIRIN         NC301B.PATH301
\\ABC\DEF\            \\ABC\DEF\
\\GHI\JKL\            \\GHI\JKL\ 
\\MNO\PQR\            \\MNO\PQR\

The statement seems to be syntactically correct but the output is not. When i create a single statement for each table the output is correct. The tables are not equal.

SELECT-PATH301

SELECT  PATH301 
FROM    NC301B
WHERE   EDIPROC like 'P30_'
    AND (LF301M > 0) 
    AND (PATH301 NOT LIKE '%saptemp%')
    AND (PATH301 NOT LIKE '%SAPTEMP%')  
    AND (PATH301 NOT LIKE '%usr%')  
    AND (PATH301 NOT LIKE '%Windows%');

SELECT-DIRIN

SELECT  DIRIN
FROM    TNC301X
WHERE   EDIPROC like 'P30_'
    AND (LASTENRIN > 0) 
    AND (DIRIN NOT LIKE '%saptemp%')
    AND (DIRIN NOT LIKE '%SAPTEMP%')  
    AND (DIRIN NOT LIKE '%usr%')  
    AND (DIRIN NOT LIKE '%Windows%');

All I want to do is to combine these statements but I don't know how.

EDIT: I got it.

FYI 

SELECT DIRIN 
FROM TNC301X 
WHERE EDIPROC like 'P30_' 
AND (DIRIN NOT LIKE '%saptemp%') 
AND (DIRIN NOT LIKE '%SAPTEMP%')
AND (DIRIN NOT LIKE '%usr%')  
AND (DIRIN NOT LIKE '%Windows%')
UNION ALL 
SELECT PATH301 
FROM NC301B 
WHERE EDIPROC like 'P30_' 
AND (PATH301 NOT LIKE '%saptemp%') 
AND (PATH301 NOT LIKE '%SAPTEMP%')
AND (PATH301 NOT LIKE '%usr%')  
AND (PATH301 NOT LIKE '%Windows%');

Upvotes: 1

Views: 124

Answers (2)

Myo Myint Aung
Myo Myint Aung

Reputation: 147

To Join two table you need to match id of two table like 'where table1.id = table2.id'. In this case I think id to join these two table is EDIPROC column. I may be wrong.

SELECT NC.PATH301,TNC.DIRIN
FROM (
      SELECT  PATH301 
      FROM    NC301B
      WHERE   EDIPROC like 'P30_'
      AND (LF301M > 0) 
      AND (PATH301 NOT LIKE '%saptemp%')
      AND (PATH301 NOT LIKE '%SAPTEMP%')  
      AND (PATH301 NOT LIKE '%usr%')  
      AND (PATH301 NOT LIKE '%Windows%')
 ) NC, 
 (   
      SELECT  DIRIN
      FROM    TNC301X
      WHERE   EDIPROC like 'P30_'
      AND (LASTENRIN > 0) 
      AND (DIRIN NOT LIKE '%saptemp%')
      AND (DIRIN NOT LIKE '%SAPTEMP%')  
      AND (DIRIN NOT LIKE '%usr%')  
      AND (DIRIN NOT LIKE '%Windows%')
 ) TNC
 WHERE NC.EDIPROC  = TNC.EDIPROC

Upvotes: 2

AsheraH
AsheraH

Reputation: 472

Union is what you are looking for. Just combine your two Select statements. If you expect to find duplicate results in your tables and you want all of them to appear in your output, use Union all More info on w3schools

Upvotes: 0

Related Questions