CODEWITHSUNDEEP
sqlsql-server
DarkW1nter
DarkW1nter

Reputation: 2841

Subquery based on column in main query

I'm looking for help structuring a SQL query with a subquery on table2 based on a column in table1, but where table1 and table2 have no relation.

something like

SELECT name, address, dateCreated,
(SELECT itemId FROM table2 WHERE itemDate BETWEEN dateCreated AND DATEADD(ss,10,dateCreated) as item
FROM table1

So for each row 'item' must be selected from table2 based on dateCreated for that row.

Upvotes: 0

Views: 53

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you want at most one item from table2, then your approach is fine but you want top (1):

SELECT t1.name, t1.address, t1.dateCreated,
       (SELECT TOP (1) t2.itemId
        FROM table2 t2
        WHERE t2.itemDate BETWEEN t1.dateCreated AND DATEADD(second, 10, t1.dateCreated
       ) as item
FROM table1 t1;

You can also phrase this as a lateral join, using outer apply:

SELECT t1.name, t1.address, t1.dateCreated,
       t2.itemId
FROM table1 t1 OUTER APPLY
     (SELECT TOP (1) t2.itemId
      FROM table2 t2
      WHERE t2.itemDate BETWEEN t1.dateCreated AND DATEADD(second, 10, t1.dateCreated
     ) t2;

This makes it easy to select multiple columns.

Upvotes: 1

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try using IF EXISTS as shown below.

SELECT name
    , [address]
    , dateCreated
FROM table1
where exits(
    SELECT itemId 
    FROM table2 WHERE itemDate BETWEEN dateCreated AND DATEADD(ss, 10,dateCreated) and table1.ItemId = table2.ItemId)

Upvotes: 2

mkRabbani
mkRabbani

Reputation: 16908

Can you please try this below logic? The way tried, will through ERROR if there are more than one records found in table2 against any row from table1.

SELECT A.name, 
A.address, 
A.dateCreated,
B.itemId 
FROM table1 A
INNER JOIN table2 B
ON B.itemDate  BETWEEN A.dateCreated AND DATEADD(ss,10,A.dateCreated)

With the above query, you will get N numbers of row for each row in table1 based the logic applied for Date --BETWEEN A.dateCreated AND DATEADD(ss,10,A.dateCreated)

Upvotes: 0

Digvijay S
Digvijay S

Reputation: 2705

SELECT name, address, dateCreated, table2.itemId 
from table1 LEFT JOIN  table2  WHERE   itemDate BETWEEN dateCreated AND DATEADD(ss,10,dateCreated)

Upvotes: 1

Related Questions