CODEWITHSUNDEEP
sqloracle-database
darkpirate
darkpirate

Reputation: 722

How to select a set of rows from a table by matching columns?

silly question. I have a table like this 

CODE        |         ID_C                                                                                                                                                           
--------------------------                                                                                                                                                           
IUS/06      |         0108                                                                                                                                                           
IUS/06      |         0108                                                                                                                                                           
MAT9        |         0124                                                                                                                                                           
MAT9        |         0124                                                                                                                                                           
MAT9        |         0124                                                                                                                                                           
PROGR12     |         0124                                                                                                                                                           
PROGR12     |         0124                                                                                                                                                           
PROGR12     |         0124                                                                                                                                                           
PROGR12     |         0124
TEC12       |         0150
SNT6        |         0150
FPN3        |         0103
BSD9        |         0103

and another table like this :

ID_C
----
0150
0108

and a last one like this : 

ID_C
----
0124

Now. I want to visualize all the rows from the first table that has id_c equal to any of the id_c from the second table AND id_c equal to the one in the third table. now the third table is more like a single datum while the second table is a collection of data. In the example this will produce in output the first table minus the row with ID_C = 0103.

Thank you for your time, this sql novice salutes you.

Upvotes: 0

Views: 58

Answers (2)

shA.t
shA.t

Reputation: 16968

Why make it so complex it can be just this:

SELECT CODE, ID_C
FROM table1st
WHERE ID_C IN (SELECT ID_C FROM table2nd)
   OR ID_C IN (SELECT ID_C FROM table3rd)

Upvotes: 2

Paul Griffin
Paul Griffin

Reputation: 2436

You'll still want an INNER JOIN, just a bit more complex one:

SELECT 
  t1.CODE,
  t1.ID_C
FROM table1 t1
INNER JOIN
  (SELECT ID_C FROM table2 t2
   UNION
   SELECT ID_C FROM table3 t3) un 
ON t1.ID_C = un.ID_C

Here's a SQLFiddle of this query in action.

There are duplicate rows in table1, but if you don't want duplicates in this result set, you can change the first SELECT to SELECT DISTINCT. Just be aware that this will incur a (sometimes severe) penalty in performance.

What I did was UNION the second and third tables together to get one big set of all the ID_C values I was interested in, then used INNER JOIN to restrict the rows of table1 to only those rows that had those values.

Technet: INNER JOIN

Technet: UNION

Upvotes: 1

Related Questions