One to many relation

Question

One to many relation

qamar 52306

SSC Enthusiast

Points: 168
More actions
September 6, 2012 at 7:43 am

#266345

I have two tables
Table1
recordid description
1001 ABC
1002 DEF
Table2
id Status time recordid
1 NEW 2hr 1001
2 PEND 1hr 1001
3 NEW 5hr 1002
i want a report that is like
recordid description status-new-time status-pend-time
how can i do this???

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ryan.mcatee SSC Eights! Points: 860 More actions · Answer 1

qamar 52306 (9/6/2012)
I have two tables
Table1
recordid description
1001 ABC
1002 DEF
Table2
id Status time recordid
1 NEW 2hr 1001
2 PEND 1hr 1001
3 NEW 5hr 1002
i want a report that is like
recordid description status-new-time status-pend-time
how can i do this???

If Status & recordid together are unique in Table2, you can use this:

SELECT r.recordid

, r.description

, sn.time AS status-new-time

, sp.time AS status-pend-time

FROM Table1 r

LEFT JOIN Table2 sn ON r.recordid=s.recordid AND Status='NEW'

LEFT JOIN Table2 sp ON r.recordid=s.recordid AND Status='PEND'

Otherwise, I think alias sn & sp should be converted to subqueries.

t.brown 89142 SSCommitted Points: 1530 More actions · Answer 2

I'd guess you'd want something like a PIVOT.

Something like

SELECT T1.recordid,

T1.description,

MAX(Case WHEN T2.Status = 'New' THEN T2.time END) AS NEwStatus,

MAX(Case WHEN T2.Status = 'PEND' THEN T2.time END) AS PendStatus

FROM Table1 AS T1 JOIN Table2 AS T2 ON T1.recordid = T2.recordid

GROUP BY T1.recordid, T1.description

t.brown 89142 SSCommitted Points: 1530 More actions · Answer 3

Or, Using the actual PIVOT statement.

SELECT recordId,

[description], [NEW], [PEND]

FROM ( SELECT T1.recordId, T1.description, T2.Status, T2.Time

FROM Table1 AS T1 JOIN Table2 AS T2 ON T1.recordid = T2.recordid ) F

PIVOT ( MAX(Time) FOR Status in ([NEW], [PEND]) ) AS P;

qamar 52306 SSC Enthusiast Points: 168 More actions · Answer 4

Thanks ryan.mcatee brother, it solved my problem

thanks for quick response