
Combining rows in SQL
Hi there. I don't know why I'm having such a problem with this task, it seems quite easy, but anyway. Let me first give you a couple of examples of data returned from the database.<BR><BR>1st row: h_id=11, p_id=123, proc_cd=90704<BR>2nd row: h_id=12, p_id=123, proc_cd=90705<BR>3rd row: h_id=13, p_id=123, proc_cd=90706<BR>4th row: h_id=14, p_id=1234, proc_cd=90704<BR><BR>In the query I am creating, I need it to return the values ONLY IF the proc_cd=90704 AND 90705 AND 90706 for each p_id. For some reason, I just cannot figure out how to specify this (I am stuck on getting the combination of proc_cds to come out correctly). Here's what I have:<BR><BR>SELECT distinct hp.p_id AS PID, COUNT(DISTINCT h_stat.h_stat_dt) AS StatDate " &_<BR>"FROM H_STAT INNER JOIN HP ON H_STAT.h_id = HP.h_id INNER JOIN H ON HP.h_id=H.h_id<BR>WHERE (H_STAT.h_stat_type= 9) AND (HP.hp_role=3) <BR>AND (h.proc_cd = 90705 and h.proc_cd=90704 and h.proc_cd=90706)<BR>GROUP BY hp.p_id<BR><BR>Whenever I try the query this way, no records are returned, although using the data above (which is also in the database), 1 record should be returned  for p_id 123 because the p_id contains each proc_cd. I've also tried using "WHERE h.proc_cd IN(90705, 90704, 90706)", but that way a record will be returned if only one of the proc_cds are found. <BR><BR>I know it has to do with combining rows, but my mind is blocked. I just cannot figure it out. I can't think of what other options I have. I know I can do separate recordsets, then check to see if each recordset has data, but I would like to only use 1 recordset (if possible). I would greatly appreciate any help.<BR><BR>Thanks in advance

RE: Combining rows in SQL
SELECT hp.p_id AS PID, COUNT(h_stat.h_stat_dt) AS StatDate " &_<BR>"FROM H_STAT INNER JOIN HP ON H_STAT.h_id = HP.h_id INNER JOIN H ON HP.h_id=H.h_id<BR>WHERE (H_STAT.h_stat_type= 9) AND (HP.hp_role=3) <BR>AND (h.proc_cd = 90705 or h.proc_cd=90704 or h.proc_cd=90706)<BR>GROUP BY hp.p_id<BR><BR>Try using or instead of and. Furthermore I can not see the any need for using distinct

RE: Combining rows in SQL
Thanks for your help. However, won't using OR instead of AND return results if only 1 of the proc_cds are found for a p_id? I need the results returned only if all of the proc_cds are found for a p_id. I know that there will be a separate row for each proc_cd, and that's my problem. How do I combine the rows?

RE: Combining rows in SQL
H_STAT ,HP,H <are they tables or aliases?<BR>if H table why you do not take any field from this table?<BR><BR>SELECT distinct hp.p_id AS PID, COUNT(DISTINCT h_stat.h_stat_dt) AS StatDate " &_<BR>"FROM H_STAT <what is it??? INNER JOIN HP<what is it??? ON H_STAT.h_id = HP.h_id INNER JOIN H <what is it??? ON HP.h_id=H.h_id<BR>WHERE (H_STAT.h_stat_type= 9) AND (HP.hp_role=3) <BR>AND (h.proc_cd = 90705 and h.proc_cd=90704 and h.proc_cd=90706)<BR>GROUP BY hp.p_id<BR><BR>SELECT distinct hp.p_id AS PID, COUNT(DISTINCT h_stat.h_stat_dt) AS StatDate " &_<BR>"FROM H_STAT,HP,H <are they tables or aliases?<BR>WHERE H_STAT.h_id = HP.h_id and HP.h_id=H.h_id and (H_STAT.h_stat_type= 9) AND (HP.hp_role=3) <BR>AND h.proc_cd = 90705 <BR>GROUP BY 1<BR><BR><BR><BR>

RE: Combining rows in SQL
Thanks for your response. H and HP are tables. I just didn't take any values from H in the example I gave you. It was just one of the options I've tried. I've tried taking the proc_cd value from H too.<BR><BR>I don't get your answer though, I need a value returned if the proc_cds 90705, 90704 and 90706 are all available for 1 p_id. The example you gave is only looking for 90705. Also, what does "GROUP BY 1" mean?

RE: Combining rows in SQL
try this:<BR>SELECT A.p_id AS PID, COUNT(B.h_stat_dt) AS StatDate,C.proc_cd " &_ <BR>"FROM H_STAT A,HP B,H C<BR>WHERE A.h_id = B.h_id and B.h_id=C.h_id and A.h_stat_type= 9 AND B.hp_role=3 <BR>AND C.proc_cd IN(90704,90705,90706) <BR>GROUP BY B.p_id <BR>or<BR>SELECT A.p_id AS PID, COUNT(B.h_stat_dt) AS StatDate,C.proc_cd " &_ <BR>"FROM H_STAT A,HP B,H C<BR>WHERE A.h_id = B.h_id and B.h_id=C.h_id and A.h_stat_type= 9 AND B.hp_role=3 <BR>AND C.proc_cd = 90705 and C.proc_cd=90704 and C.proc_cd=90706 <BR>try with and without:<BR>GROUP BY B.p_id <BR>

RE: Combining rows in SQL
Well, I misunderstood your problem :)<BR><BR>I think you need to do something like this<BR><BR>SELECT hp.p_id AS PID, COUNT( h_stat.h_stat_dt) AS StatDate " &_ <BR> "FROM H_STAT INNER JOIN HP ON H_STAT.h_id = HP.h_id INNER JOIN H as H1 ON HP.h_id=H.h_id <BR> WHERE (H_STAT.h_stat_type= 9) AND (HP.hp_role=3) <BR> AND 3 = (select count(distinct proc_id) from h where proc_id in (90704,90705,90706) and h_id = h1.h_id)<BR>GROUP BY hp.p_id <BR><BR>If your DBMS does not support that, you may try something like this<BR><BR>SELECT hp.p_id AS PID, COUNT( h_stat.h_stat_dt) AS StatDate <BR> FROM H_STAT INNER JOIN HP ON H_STAT.h_id = HP.h_id INNER JOIN H as H1 ON HP.h_id=H.h_id <BR> WHERE (H_STAT.h_stat_type= 9) AND (HP.hp_role=3) <BR> AND exists ( select 1 from h where proc_id = 90705 and h_id = h1.h_id)<BR> and exists ( select 1 from h where proc_id = 90704 and h_id = h1.h_id)<BR> and exists ( select 1 from h where proc_id = 90706 and h_id = h1.h_id)<BR>GROUP BY hp.p_id <BR><BR>or you could do it with multiple selfjoins (if you are using Mysql)<BR>

I figured it out
Thanks for all the help, but I ended up finding the solution!
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

