sql help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sql help

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-07-13 : 14:51:03

Table:SIN

SID SEID FMID TP
--- ---- --- ---
126 4000 5107 A
115 NULL 5107 B
116 NULL 5107 B

Table:HIT

RNRSNP SID
------ ----
08 116

i want the below output.plese help me in writing the query

RNRSNP SID SEID
------ ---- -----
08 116 4000

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-13 : 14:56:34

Could you explain the expected result set? 4000 is in the 126 row, so how does it get associated with the 116 row?

Tara Kizer
aka tduggan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-13 : 15:25:46

[code]-- prepare test data
declare @sin table (SID tinyint, SEID smallint, FMID smallint, TP char(1))

insert @sin
select 126, 4000, 5107, 'A' union all
select 115, NULL, 5107, 'B' union all
select 116, NULL, 5107, 'B'

declare @HIT table (RNRSNP char(2), SID tinyint)

insert @HIT
select '08', 116

-- Do the work
SELECT h.RNRSNP,
h.SID,
z.SEID
FROM (
SELECT s.SID,
w.SEID
FROM (
SELECT FMID,
MAX(SEID) SEID
FROM @SIN
GROUP BY FMID
) w
INNER JOIN @sin s ON s.FMID = w.FMID
) z
INNER JOIN @HIT h ON h.SID = z.SID[/code]

Peter Larsson
Helsingborg, Sweden

humanpuck
Yak Posting Veteran

94 Posts

Posted - 2006-07-13 : 15:30:06

quote:
Originally posted by tkizer

Could you explain the expected result set? 4000 is in the 126 row, so how does it get associated with the 116 row?

Tara Kizer
aka tduggan

I agree this is what baffled me...why bother writing a query against the tbls if you just want a specific result set, create a tbl and populate it accordingly.

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2006-07-13 : 21:29:54

Thanks peter !!.
I need to implement the same query logic in below stored procedure which i am not able to display the correct output based on SEID.
Problem with below procedure when i passing the value SEID parameter the output result is showing 0.

Select REARSNPD.DSC 'REARSNPD',
IsNull(Counts.REARSNPD_COUNT, 0) As REARSNPD_COUNT
From REARSNPD
LEFT JOIN
(
SELECT B.DSC,
COUNT(A.RSNP) AS REARSNPD_COUNT
FROM HIT A
RIGHT OUTER JOIN RRNP b ON b.RNPCD = A.RRNP
LEFT JOIN CDHC E ON A.SID=E.SID
LEFT OUTER JOIN EX C ON C.EXCD=E.BGLEX
** INNER JOIN SIN D ON D.SID=A.SID
WHERE (@EX=0 OR E.BGLEX=@EX)
AND (@FROMDATE IS NULL OR D.SDT Between @FROMDATE And @TODATE)
AND (@TODATE IS NULL OR D.SDT Between @FROMDATE And @TODATE)
AND (@SEID=0 OR D.SE_ID=@SEID)
AND (@ONLYPE=0 OR A.HLT=@ONLYPE)
GROUP BY B.DSC
) AS Counts On REARSNPD.DSC = Counts.DSC

with the inner join in the above query ** (INNER JOIN SIN D ON D.SID=A.SID)
it will fetch only matching records.since SEID is null for matching record is displaying 0.I want to display a count a as 1 for spid 116 since its has the value 08 for RNRSNP HIT table.when a parameter is passed with seid 4000.

Relation:
A FMID can have one A tp with seid and same FMID may have multiple tp B's in the table SIN.The values of SID B's may or may not exists in table HIT. if exists then it will be SID value with RNRSNP value.

Need to identify the count based on the table hit.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources