Using a Select Statement Within a Where Clause

Using a SELECT statement within a WHERE clause

It's called correlated subquery. It has it's uses.

SQL select statement in where clause

You will be getting multiple columns from the sub query when I assume you only want the id column:

SELECT *
FROM   table
WHERE  id IN (SELECT id
          FROM   table
          WHERE  description = 'A')
   AND description = 'B' 

Select statement with a where clause and add 'comment' in another column

Use UNION ALL to combine multiple (compatible) result sets, and you can add extra expressions in the SELECT clause

SELECT [Number],[Dep],[Join M],[Join B],[Resign M ],[Resign B],'Main' as Comment
FROM [Table] WHERE [Dep]        = 'M'      AND 
                            ([Join M]   <> [Join B] OR   
                            [Resign M]  <> [Resign Date Beneficiary]) 
UNION ALL

SELECT [Number],[Dep],[Join M],[Join B],[Resign M ],[Resign B],'Date' as Comment
FROM [Table] WHERE [Join B]   < [Resign B]  AND
                            ([Join M]  > [Join B]    OR 
                            [Resign M] < [Resign B])

Alternatively, if these rows represent the entire contents of Table and you just want to classify rows as either Main or Date, you could use a CASE expression:

SELECT [Number],[Dep],[Join M],[Join B],[Resign M ],[Resign B],
  CASE WHEN [Dep]        = 'M'      AND 
            ([Join M]   <> [Join B] OR   
            [Resign M]  <> [Resign Date Beneficiary])
  THEN 'Main'
  ELSE 'Date' END as Comment
FROM [Table]

How can i have where clause in Nested queries with select statement that returns more than 1 records

Is there a reason why you are using subselects to accomplish this?

The regular way to do this is with joins:

select e.*, ats.entity_internal_name
  from entity e
  join attribute a
    on a.attribute_entity_id = e.id
  join attribute_type_specification ats
    on ats.id = a.attribute_type_specification_id
 where ats.entity_internal_name = 'country'
 order by e.entity_id 
;

Where clause on subquery statement in select

Just move it to a derived query. You cannot use a column defined in the SELECT clause in the WHERE clause.

Select col1, col2, records
from
(
    Select col1, 
           col2, 
           (select ..... from table2) as records
    from table1
) X
where records is not null;

Inner joining to a select statement where the inner select statement's where clause references the outer select?

Looks like you have your JOIN reference in the wrong place.

SELECT S.Item, S.SerialNum, S.ReceiveDate
FROM SALES S
INNER JOIN 
(
    SELECT W.Item, W.SerialNum, MIN(W.SalesDate) MinSalesDate
    FROM WARRANTY W
    GROUP BY Item, SerialNum
) WW
        ON S.Item = WW.Item 
       AND S.SerialNum = WW.SerialNum

Edit, based on your comment about filtering, you can place a WHERE clause on your inner SELECT:

SELECT S.Item, S.SerialNum, S.ReceiveDate, WW.MinSalesDate
FROM SALES S
INNER JOIN 
(
    SELECT W.Item, W.SerialNum, MIN(W.SalesDate) MinSalesDate
    FROM WARRANTY W
    WHERE yourFilter here
    GROUP BY Item, SerialNum
) WW
        ON S.Item = WW.Item 
       AND S.SerialNum = WW.SerialNum

SQL left join on select statements with where clause

This should also work:

select
   tab1.foo_id
  ,tab1.start_date
  ,tab1.end_date
  ,sum(tab2.total) as total
  ,sum(tab2.worker) as worker
from foo tab1
left join bars tab2
  on tab2.work_date between tab1.start_date and tab1.end_date
  and tab1.foo_id = tab2.id
where tab1.rownum <= 10
group by
   tab1.foo_id
  ,tab1.start_date
  ,tab1.end_date

---

Related Topics