need help for query - 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 2012 Forums

Transact-SQL (2012)

need help for query

Author

Topic

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2014-08-01 : 08:43:42

hi all,

i need your help for following query, when we execute the query the following result is getting.

SELECT COURIERCODE,WAYBILLSTARTSERIES,WAYBILLENDSERIES FROM waybill_master
WHERE COURIERCODE IN( SELECT COURIERCODE FROM waybill_master WHERE waybillstartseries=waybillendseries)
ORDER BY WAYBILLSTARTSERIES,WAYBILLENDSERIES DESC

getting result
-----

'MAR', '25', '25'
'MAR', '26', '200'
'MAR', '26', '50'
'SUN', '100', '100'
'MOON', '200', '200'
'MOON', '201', '250'
'VENUS', '500', '500'
'EARTH', '200','200'
'EARTH', '500','500'
we need result
----------------
'SUN', '100', '100'
'VENUS', '500', '500'
'EARTH', '200','200'
'EARTH', '500','500'

IF ANY RECORD EQUAL WE NEED ONLY THAT DATA OR IF THE RECORD WITH THE SAME NAME WITH NOT EQUAL SHOULD NOT APPEAR. KINDLY NEED HELP.

THANKS

RAJNIDAS

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-01 : 08:52:46

SELECT COURIERCODE,WAYBILLSTARTSERIES,WAYBILLENDSERIES FROM waybill_master
WHERE COURIERCODE IN( SELECT COURIERCODE FROM waybill_master WHERE waybillstartseries=waybillendseries) -- this is getting you every value where they are equal, but that just gets you the code - you then select all records with that code -- you don't need this
ORDER BY WAYBILLSTARTSERIES,WAYBILLENDSERIES DESC

that is like
Select * from waybill_master
WHERE COURIERCODE IN ('EARTH','VENUS','SUN'... etc) you completely lose the fact that you only want the value where they are equal

this should work
SELECT COURIERCODE,WAYBILLSTARTSERIES,WAYBILLENDSERIES
FROM waybill_master
WHERE WAYBILLSTARTSERIES = WAYBILLENDSERIES --- you will only get value back where they are equal
ORDER BY DWAYBILLSTARTSERIES,WAYBILLENDSERIES DESC

rajnidas
Yak Posting Veteran

97 Posts

Posted - 2014-08-01 : 08:59:58

hi all,

IF ANY RECORD EQUAL WE NEED ONLY THAT DATA and if any records more than one records with same name with out unequal that records should not required.

thanks for reply

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-02 : 07:02:28

Are you saying you need only records that have WAYBILLSTARTSERIES = WAYBILLENDSERIES , but if the record also has a record where WAYBILLSTARTSERIES != WAYBILLENDSERIES you don't want either of the records back for that COURIERCODE . Or is it just all records where WAYBILLSTARTSERIES = WAYBILLENDSERIES - which I had sent in my prior post?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-08-02 : 09:39:08

Answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=195513

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources