CODEWITHSUNDEEP
sql
Stefan
Stefan

Reputation: 3859

Combine two different select statements, one distinct the other not

I have two selects which are required to filter data. They are not complicated:

"SELECT * FROM StevesTable t WHERE "
"t.data1 = '%s' AND "
"t.data2 = to_date('%s','DD/MM/YYYY');",
strdata1,
dtDate.Format();

and 

SELECT distinct data1 FROM anothertable ftt
join table1 tab on tab.somedata = ftt.somedata
where tab.somedata = 0
and tab.someotherdata = 1

I would like to combine these two as I need to filter the returned dataset from the first select statement by the returned field in the second (ie if a record returned in the first set does not have a data1 value which is contained in the second returned set it is invalid).

I tried to union and intersect the selects but you need the same number of columns returned and that cannot happen as these are completely different tables. When I tried to simply merge them together I found it difficult as the second select statement is a distinct select whereas the first is not.

I was wondering whether I had missed a trick somewhere for combining these sorts of selects?

Upvotes: 1

Views: 6390

Answers (2)

user359040
user359040

Reputation:

You can do this using an EXISTS condition:

SELECT * FROM StevesTable t 
WHERE t.data1 = '%s' AND 
      t.data2 = to_date('%s','DD/MM/YYYY') AND
      EXISTS (select null
              from anothertable ftt
              join table1 tab on tab.somedata = ftt.somedata
              where tab.somedata = 0 and 
                    tab.someotherdata = 1 and
                    ftt.data1 = t.data1)

Upvotes: 3

huelbois
huelbois

Reputation: 7012

What you need is a SQL sub-query:

SELECT * FROM StevesTable t 
WHERE t.data1 = '%s' 
AND t.data2 = to_date('%s','DD/MM/YYYY')
AND t.data1 in (select distinct data1 FROM anothertable ftt
    join table1 tab on tab.somedata = ftt.somedata
    where tab.somedata = 0
    and tab.someotherdata = 1)

There, you check that all records in the first select have a data1 value in the second set.

Upvotes: 4

Related Questions