February 23, 2011 at 12:59 pm
Hi, i am trying to solve this issue without result.
I want to display one record by Id from A.
Let sayt i have:
insert into #TempA values(1,'AAA')
insert into #TempA values(2,'BBB')
create Table #TempC (id_c int, NameC nvarchar(10))
insert into #TempC values(1,'963')
insert into #TempC values(2,'904')
insert into #TempC values(3,'965')
create Table #TempB (fkid_a int, fkid_C int, Name nvarchar(10))
insert into #TempB values(1,1,'ZZZ')
insert into #TempB values(1,2,'YYY')
insert into #TempB values(1,3,'XXX')
insert into #TempB values(2,2,'WWW')
insert into #TempB values(3,2,'VVV')
select Distinct #TempA.NameA, #TempC.NameC
from #TempA inner join #TempB on
id_a = fkid_a inner join #TempC on fkid_C = id_c
drop table #TempA
drop table #TempB
drop table #TempC
After run i got this result:
NameA NameC
---------- ----------
AAA 904
AAA 963
AAA 965
BBB 964
(4 row(s) affected)
But i want to get:
AAA 904
BBB 964
The businees rule is, if id_c = 2 , i have to take this row, otherwise i can take any records.
But i have trouble to do it in the select query.
Any idea????
Your help will be appreciate.
Thanks
February 23, 2011 at 1:13 pm
I believe your select query should look like:
select distinct #TempA.NameA, #TempC.NameC
from #TempA
inner join #TempB
on id_a = fkid_a
inner join #TempC
on fkid_C = id_c and id_c = 2
_________________________________
seth delconte
http://sqlkeys.com
February 23, 2011 at 2:00 pm
Thank you Delconte for your answer, but it does not works, according to your query , we will get anytime the row with
id_c = 2.
But i want if id_c = 2 i will consider this row.
so if i insert another row in these tables:
create Table #TempA (id_a int, NameA nvarchar(10))
insert into #TempA values(1,'AAA')
insert into #TempA values(2,'BBB')
insert into #TempA values(4,'CCC')
create Table #TempC (id_c int, NameC nvarchar(10))
insert into #TempC values(1,'963')
insert into #TempC values(2,'904')
insert into #TempC values(3,'965')
insert into #TempC values(4,'965')
create Table #TempB (fkid_a int, fkid_C int, Name nvarchar(10))
insert into #TempB values(1,1,'ZZZ')
insert into #TempB values(1,2,'YYY')
insert into #TempB values(1,3,'XXX')
insert into #TempB values(2,3,'WWW')
insert into #TempB values(3,2,'VVV')
insert into #TempB values(4,3,'YYY')
select Distinct #TempA.NameA, #TempC.NameC
from #TempA inner join #TempB on
id_a = fkid_a inner join #TempC on fkid_C = id_c
select distinct #TempA.NameA, #TempC.NameC
from #TempA
inner join #TempB
on id_a = fkid_a
inner join #TempC
on fkid_C = id_c and id_c = 2
drop table #TempA
drop table #TempB
drop table #TempC
We suppose to have:
NameA NameC
---------- ----------
AAA 904
BBB 965
CCC 965
Not only:
NameA NameC
---------- ----------
AAA 904
Cheers
February 23, 2011 at 10:09 pm
SELECT A.NameA, C.NameC
FROM #TempA AS A
CROSS
APPLY (
SELECT TOP (1)
B.fkid_C
FROM #TempB AS B
WHERE B.fkid_a = A.id_a
ORDER BY
B.fkid_C DESC
) AS B
JOIN #TempC AS C ON C.id_c = B.fkid_C;
February 24, 2011 at 6:40 am
Hi SSCrazy Eights,
thanks for your answer, but the result is not that i suppose to get.
Your rersult:
NameA NameC
---------- ----------
AAA 965
BBB 965
CCC 965
My result:
NameA NameC
---------- ----------
AAA 904
BBB 965
CCC 965
Thank you.
February 24, 2011 at 6:58 am
Mosafi (2/24/2011)
thanks for your answer, but the result is not that i suppose to get.
I noticed that, but assumed it was an error in your test data.
TableA value 1 matches three records in TableB, with values 1,2,3 for link to TableC.
Highest value is 3, which links to value 965 in TableC.
You need to be clearer about the logic.
February 24, 2011 at 8:35 am
Hi SSCrazy,
There is misunderstanding about the rule business.
I am not talking about Highest value as you said, so 3 is not the default value.
As i said in the previous post, default value is id_c = 2, in this case i will consider the current row:
insert into #TempC values(1,'963')
insert into #TempC values(2,'904')
insert into #TempC values(3,'965')
insert into #TempC values(4,'965')
AAA 904
AAA 963
AAA 965
BBB 965
CCC 965
So for each row in the result i have this test:
AAA:
if id_c = 2 then
AAA, 904 will be take
else
any row
BBB, CCC:
if id_c = 2 then
on this case nothing
else
any row (BBB,965| CCC,965) will be take
Thanks.
February 24, 2011 at 8:50 am
Right.
SELECT A.NameA, C.NameC
FROM #TempA AS A
CROSS
APPLY (
SELECT TOP (1)
B.fkid_C
FROM #TempB AS B
WHERE B.fkid_a = A.id_a
ORDER BY
CASE
WHEN B.fkid_C = 2 THEN 0
ELSE 1
END
) AS B
JOIN #TempC AS C ON C.id_c = B.fkid_C
;
February 24, 2011 at 9:47 am
Wonderful SSCrazy, it's works now.
I was trying to solve the issue with the RANK notion, but i still looking. Anyway with the CROSS APPLY in your solution it's fine. I will learn something new today after i will read more on this ACCROS APPLY.
Thanks
February 24, 2011 at 10:49 am
Mosafi (2/24/2011)
I will learn something new today after i will read more on this ACCROS APPLY.
There's an article on this site about APPLY, and I know the author:
http://www.sqlservercentral.com/articles/APPLY/69953/ << Part 1
http://www.sqlservercentral.com/articles/APPLY/69954/ << Part 2
February 24, 2011 at 11:47 am
Once again , thanks.:-)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply