CODEWITHSUNDEEP
sqloracle-database
Dominic Bou-Samra
Dominic Bou-Samra

Reputation: 15416

Edit: QUESTION CHANGED - SQL: Taking all rows from table where column names are NOT in another table

Never done SQL before (how that's possible I don't know),

I have two tables, like so:

Table1:

Column Names:    A    B    C
Rows:            1  sdf  sdsd
                 2  seg  werr

and

Table2:

Column Names:    A    B    C     D    E    F
Rows:            1    sdf  sdsd  yuj  uui  ddd
                 1    sdf  sdsd  sss  sdd  ssw
                 1    sdf  sdsd  jut  scv  sef
                 2    seg  werr  oel  ewe  wee
                 2    seg  werr  ujf  etr  wuk
                 2    seg  werr  los  hjd  wee

EDIT: Question changed.

How do I take all rows with the columns that are unique in the 2nd table? I.e. I only want the data from D, E and F where the values in A, B, C in Table1 correspond to the A, B, C values in Table2.

So for instance given A=1 and B=sdf, I want the rows:

sdsd  yuj  uui  ddd
sdsd  sss  sdd  ssw
sdsd  jut  scv  sef

I could just go: SELECT * FROM Table2 WHERE A='1' AND B='sdf' but I would get 

1    sdf  sdsd  yuj  uui  ddd
1    sdf  sdsd  sss  sdd  ssw
1    sdf  sdsd  jut  scv  sef

Upvotes: 0

Views: 145

Answers (5)

Benoit
Benoit

Reputation: 79185

It seems that here you have one of the only cases where a NATURAL JOIN is the proper solution since your column names are exactly the same in both tables.

 SELECT D, E, F
   FROM table1
NATURAL JOIN table2
  WHERE a = '1'
    AND b = 'sdf'
    AND c = 'sdsd'

Upvotes: 0

ThinkJet
ThinkJet

Reputation: 6735

Just list columns you want to select.

So for instance given A='1' and B='sdf' :

SELECT C,D,E,F FROM Table2 WHERE A='1' AND B='sdf'

Upvotes: 1

Charles Graham
Charles Graham

Reputation: 1157

The most direct way would be:

Select D, E From Table2

If you are looking for something dynamic, the solution will depend on which database vendor you are using. There is not an agnostic dynamic solution.

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146499

Generally, You need to access the system tables or the catalog views to do this. The exact syntax would depend on what DBMS you are using...

Upvotes: 0

staticsan
staticsan

Reputation: 30555

You want a LEFT JOIN (or RIGHT JOIN) with a special operation in the WHERE to look for non-matching values.

In this case, something like this:

SELECT Table2.*
FROM Table2 LEFT JOIN Table1 USING (SomeCommonField)
WHERE Table1.SomeCommonField IS NULL

Upvotes: 0

Related Questions