sql server programming:
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are. Sample data is also a good idea, along with clear specifications.
You don't use a subquery you do a join. SELECT m.*, d.Det1, d.Det2 FROM master AS m INNER JOIN Detail AS d ON m.Key = d.Key -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Ganesh" <gsganesh@yahoo.com> wrote in message
news:uCkVTWVaFHA.3184@TK2MSFTNGP15.phx.gbl...
>I want to run a correlated sub query something like this
>
> Select * from master m
> where m.key
> exists
> select top 1 det1, det2
> from detail d where m.key = d.key
>
>
> Now i want to return the det1 and det2 field values with all master table
> values
>
> how can i do this
>
> Thanks
>
I want to run a correlated sub query something like this
Select * from master m
where m.key
exists
select top 1 det1, det2
from detail d where m.key = d.key
Now i want to return the det1 and det2 field values with all master table
values
how can i do this
Thanks
OK, then another choice would be something like this. You need to see which is most efficient in your environment. SELECT m.*, d. (select top 1 det1 from detail d where d.key = m.key ORDER BY d.F2 DESC) AS Det1, (select top 1 det2 from detail d where d.key = m.key ORDER BY d.F2 DESC) AS Det1 FROM master AS m -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Ganesh" <gsganesh@yahoo.com> wrote in message
news:%23lO5tHbaFHA.2128@TK2MSFTNGP14.phx.gbl...
>I can not use inner join, becuase it will return all matching record of
>detail with master
>
> for eg i need
>
>
> my master table contain
> f1 field f1 is a primary key
> values are
> 1
> 2
> 3
> 4
> 5
>
> and the detail table contain f1 int,f2 datetime, f3 varchar(100) detail
> contains records
> 1, 1 jan 2005, first
> 1, 2 jun 2005, second
> 1, 4 jun 2005, third
> 5, 2 feb 2005, fourth
>
> now i want to return
> 1, 1jan 2005, first
> 5, 2 feb 2005, fourth
>
> Thanks
>
>
>
>
>
>
>
>
>
>
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
> news:Ov0fU%23VaFHA.580@TK2MSFTNGP15.phx.gbl...
>> You don't use a subquery you do a join.
>>
>> SELECT m.*, d.Det1, d.Det2 FROM master AS m INNER JOIN Detail AS d
>> ON m.Key = d.Key
>>
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>>
>> "Ganesh" <gsganesh@yahoo.com> wrote in message
>> news:uCkVTWVaFHA.3184@TK2MSFTNGP15.phx.gbl...
>>>I want to run a correlated sub query something like this
>>>
>>> Select * from master m
>>> where m.key
>>> exists
>>> select top 1 det1, det2
>>> from detail d where m.key = d.key
>>>
>>>
>>> Now i want to return the det1 and det2 field values with all master
>>> table values
>>>
>>> how can i do this
>>>
>>> Thanks
>>>
>>
>>
>
>
I can not use inner join, becuase it will return all matching record of detail with master for eg i need my master table contain f1 field f1 is a primary key values are 1 2 3 4 5 and the detail table contain f1 int,f2 datetime, f3 varchar(100) detail contains records 1, 1 jan 2005, first 1, 2 jun 2005, second 1, 4 jun 2005, third 5, 2 feb 2005, fourth now i want to return 1, 1jan 2005, first 5, 2 feb 2005, fourth Thanks [quoted text, click to view] "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:Ov0fU%23VaFHA.580@TK2MSFTNGP15.phx.gbl...
> You don't use a subquery you do a join.
>
> SELECT m.*, d.Det1, d.Det2 FROM master AS m INNER JOIN Detail AS d
> ON m.Key = d.Key
>
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Ganesh" <gsganesh@yahoo.com> wrote in message
> news:uCkVTWVaFHA.3184@TK2MSFTNGP15.phx.gbl...
>>I want to run a correlated sub query something like this
>>
>> Select * from master m
>> where m.key
>> exists
>> select top 1 det1, det2
>> from detail d where m.key = d.key
>>
>>
>> Now i want to return the det1 and det2 field values with all master table
>> values
>>
>> how can i do this
>>
>> Thanks
>>
>
>
Hi One solution: Select m.f1,m.f2,m.f3 from detail m JOIN (select f1, min(f2)as F2 from detail group by f1 ) d ON d.f1 = m.f1 AND m.f2 = d.f2 Another: Select m.f1,m.f2,m.f3 from detail m WHERE f2 = (select min(f2) from detail d WHERE d.f1 = m.f1 ) John [quoted text, click to view] "Ganesh" <gsganesh@yahoo.com> wrote in message
news:%23lO5tHbaFHA.2128@TK2MSFTNGP14.phx.gbl...
>I can not use inner join, becuase it will return all matching record of
>detail with master
>
> for eg i need
>
>
> my master table contain
> f1 field f1 is a primary key
> values are
> 1
> 2
> 3
> 4
> 5
>
> and the detail table contain f1 int,f2 datetime, f3 varchar(100) detail
> contains records
> 1, 1 jan 2005, first
> 1, 2 jun 2005, second
> 1, 4 jun 2005, third
> 5, 2 feb 2005, fourth
>
> now i want to return
> 1, 1jan 2005, first
> 5, 2 feb 2005, fourth
>
> Thanks
>
>
>
>
>
>
>
>
>
>
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
> news:Ov0fU%23VaFHA.580@TK2MSFTNGP15.phx.gbl...
>> You don't use a subquery you do a join.
>>
>> SELECT m.*, d.Det1, d.Det2 FROM master AS m INNER JOIN Detail AS d
>> ON m.Key = d.Key
>>
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>>
>> "Ganesh" <gsganesh@yahoo.com> wrote in message
>> news:uCkVTWVaFHA.3184@TK2MSFTNGP15.phx.gbl...
>>>I want to run a correlated sub query something like this
>>>
>>> Select * from master m
>>> where m.key
>>> exists
>>> select top 1 det1, det2
>>> from detail d where m.key = d.key
>>>
>>>
>>> Now i want to return the det1 and det2 field values with all master
>>> table values
>>>
>>> how can i do this
>>>
>>> Thanks
>>>
>>
>>
>
>
Don't see what you're looking for? Try a search.
|
