query 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 2005 Forums

Transact-SQL (2005)

query help

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-02 : 04:51:30

SELECT ET.TASK_ID, ETL.TASK_NAME, isnull(ET.ID_VALUE, '0') AS ID_VALUE
FROM EMRCOTransactions ET, EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID in (5,12)
--GROUP BY ET.ID_VALUE
UNION ALL

SELECT ET.TASK_ID, ETL.TASK_NAME, isnull(ET.ID_VALUE, '0') AS ID_VALUE
FROM EMRCOTransactions ET INNER JOIN EMRTaskListLkup ETL ON ETL.TASK_ID = ET.TASK_ID
LEFT OUTER JOIN USERS US ON ET.USER_LOGIN = US.USER_LOGIN
WHERE ET.TASK_ID = 73

my query is like thid but am getting values as

12 INCOMING NOTES 1512
12 INCOMING NOTES 1516
12 INCOMING NOTES 1519
12 INCOMING NOTES 1543
12 INCOMING NOTES 1544
12 INCOMING NOTES 1552
5 REFILL 1581
5 REFILL 1589
12 INCOMING NOTES 1582
12 INCOMING NOTES 1550
12 INCOMING NOTES 1615
12 INCOMING NOTES 1640
5 REFILL 1697
12 INCOMING NOTES 1517
73 MED RENEWAL 1503

i want only one one value for each task_id like for 5 one,12 one,73 one

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-02 : 04:56:30

Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-02 : 04:57:57

Also what is your comment on this post
You didn't tell us if you got an answer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138934

Madhivanan

Failing to plan is Planning to fail

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-02 : 04:58:06

can you give me the query in doing so please

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-02 : 04:59:53

quote:
Originally posted by rajasekhar857

can you give me the query in doing so please

The link has four queries
Make use of them

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 05:02:32

quote:
Originally posted by rajasekhar857

SELECT ET.TASK_ID, ETL.TASK_NAME, isnull(ET.ID_VALUE, '0') AS ID_VALUE
FROM EMRCOTransactions ET, EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID in (5,12)
--GROUP BY ET.ID_VALUE
UNION ALL

SELECT ET.TASK_ID, ETL.TASK_NAME, isnull(ET.ID_VALUE, '0') AS ID_VALUE
FROM EMRCOTransactions ET INNER JOIN EMRTaskListLkup ETL ON ETL.TASK_ID = ET.TASK_ID
LEFT OUTER JOIN USERS US ON ET.USER_LOGIN = US.USER_LOGIN
WHERE ET.TASK_ID = 73

my query is like thid but am getting values as

12 INCOMING NOTES 1512
12 INCOMING NOTES 1516
12 INCOMING NOTES 1519
12 INCOMING NOTES 1543
12 INCOMING NOTES 1544
12 INCOMING NOTES 1552
5 REFILL 1581
5 REFILL 1589
12 INCOMING NOTES 1582
12 INCOMING NOTES 1550
12 INCOMING NOTES 1615
12 INCOMING NOTES 1640
5 REFILL 1697
12 INCOMING NOTES 1517
73 MED RENEWAL 1503

i want only one one value for each task_id like for 5 one,12 one,73 one

if you want unique combination use union instead of union all

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-02 : 05:05:00

quote:
Originally posted by visakh16

quote:
Originally posted by rajasekhar857

SELECT ET.TASK_ID, ETL.TASK_NAME, isnull(ET.ID_VALUE, '0') AS ID_VALUE
FROM EMRCOTransactions ET, EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID in (5,12)
--GROUP BY ET.ID_VALUE
UNION ALL

SELECT ET.TASK_ID, ETL.TASK_NAME, isnull(ET.ID_VALUE, '0') AS ID_VALUE
FROM EMRCOTransactions ET INNER JOIN EMRTaskListLkup ETL ON ETL.TASK_ID = ET.TASK_ID
LEFT OUTER JOIN USERS US ON ET.USER_LOGIN = US.USER_LOGIN
WHERE ET.TASK_ID = 73

my query is like thid but am getting values as

12 INCOMING NOTES 1512
12 INCOMING NOTES 1516
12 INCOMING NOTES 1519
12 INCOMING NOTES 1543
12 INCOMING NOTES 1544
12 INCOMING NOTES 1552
5 REFILL 1581
5 REFILL 1589
12 INCOMING NOTES 1582
12 INCOMING NOTES 1550
12 INCOMING NOTES 1615
12 INCOMING NOTES 1640
5 REFILL 1697
12 INCOMING NOTES 1517
73 MED RENEWAL 1503

i want only one one value for each task_id like for 5 one,12 one,73 one
if you want unique combination use union instead of union all

It wont help
He needs only one row for each task Id
There are no duplicates as far as all columns are considered

Madhivanan

Failing to plan is Planning to fail

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-02 : 05:06:16

yes exactly

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-02 : 05:08:04

quote:
Originally posted by rajasekhar857

yes exactly

Did you read the link I posted?
It exactly will do what you want

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 05:08:29

quote:
Originally posted by rajasekhar857

yes exactly

then you need to use solutions in link

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-02 : 05:42:59

Hi i created a view as mentioned above but when i am using like this
SELECT TOP 1 TASK_ID, TASK_NAME,ID_VALUE FROM EMRABC WHERE TASK_ID in (5,12,73)
giving only one value rather than three values

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 05:46:12

quote:
Originally posted by rajasekhar857

Hi i created a view as mentioned above but when i am using like this
SELECT TOP 1 TASK_ID, TASK_NAME,ID_VALUE FROM EMRABC WHERE TASK_ID in (5,12,73)
giving only one value rather than three values

you're selecting only one record out of them. then how will you get all the three?

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-02 : 05:51:15

then what is the way to do so am totally confused with this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 05:53:35