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.
Author |
Topic |
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-14 : 21:23:54
|
Hi Guys,Here is my Sample Table and DataCreate Table TableA( ID INT, Gender varchar(10), BP varchar(50) )Create Table TableB( ID Int, City varchar(20), BP1 varchar(20), BP2 Varchar(20))Insert Into TableAvalues ('1','Male','140/90')Insert Into TableAvalues ('2','Male','130/80')Insert Into TableAvalues ('3','Male','140/75')Insert Into TableAvalues ('4','Male','112/90')Insert Into TableBValues ('1','NY','140','90')Insert Into TableBValues ('10','NY','140','90')Insert Into TableBValues ('11','NY','140','75')Insert Into TableBValues ('4','NY','112','90')Select * from TableASelect * from TableBSelect A.*, B.* From TableA A Inner Join TableB B On A.ID = B.ID Where A.BP <= ??? My Requirement IS If BP is <= 14/90. I want to Compare TABLEA.BP <= TABLEB.BP1 & TABLEB.BP2.I know its gonna use Substring Function.End Result should be ID Gender BP ID City BP1 BP21 Male 140/90 1 NY 140 90Thanks for help. |
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-14 : 21:51:32
|
I got Half Solution.Select A.*, B.* From TableA A Inner Join TableB B On A.ID = B.ID Where B.BP1 <= SUBSTRING(BP,1,CHARINDEX('/',BP)-1) AND B.BP2 <= LTRIM(RIGHT(BP, CHARINDEX('/', BP + '/')-2) )My requirement is Matching row from both table and BP Less then 140/90..Any advise? |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-14 : 21:57:23
|
Just for update I can useSelect A.*, B.* From TableA A Inner Join TableB B On A.ID = B.ID Where SUBSTRING(BP,1,CHARINDEX('/',BP)-1) = '140' AND LTRIM(RIGHT(BP, CHARINDEX('/', BP + '/')-2) ) = '90'to get the result but the point is i don't want to use hard coded value, i have to use Field value. let say 2morrow value change. At least my query runs fine.... |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-14 : 22:07:56
|
Guys Want to give you update. I got itHere is the query is some one want to know...Select A.*, B.* From TableA A Inner Join TableB B On A.ID = B.ID Where SUBSTRING(BP,1,CHARINDEX('/',BP)-1)> B.BP1 AND LTRIM(RIGHT(BP, CHARINDEX('/', BP + '/')-2) ) > B.BP2Please feel free to point out if something wrong.Thank You. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-02-14 : 22:15:51
|
you should store the BP in integer else when you are doing a string comparison and '90' will be consider greater than '100' ; with tblA as( Select *, BP1 = convert(int, left(BP, charindex('/', BP) - 1)), BP2 = convert(int, right(BP, len(BP) - charindex('/', BP))) from TableA),tblB as( -- here i have convert it to integer so that the comparison make sense Select ID, City, BP1 = convert(int, BP1), BP2 = convert(int, BP2) from TableB)select *from tblA a inner join tblB b on a.ID = b.IDWHERE a.BP1 <= 140and a.BP2 <= 90and b.BP1 <= 140and b.BP2 <= 90 KH[spoiler]Time is always against us[/spoiler] |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-14 : 22:21:39
|
Kh Thank you for your reply. Like i said i don't want to use hard coded value. I really want to use actual field. So someone change the data. at least my query runs fine... |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-15 : 01:06:15
|
Guys just find out this query is giving right value for meSelectA.*,B.*From TableA AInner Join TableB B On A.ID = B.IDWhere SUBSTRING(BP,1,CHARINDEX('/',BP)-1) = '140'AND LTRIM(RIGHT(BP, CHARINDEX('/', BP + '/')-2) ) = '90'Above query is giving me if one value match giving me record. How i bound it if both condition match then give me resule? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-02-17 : 23:46:54
|
quote: Originally posted by Sonu619 Kh Thank you for your reply. Like i said i don't want to use hard coded value. I really want to use actual field. So someone change the data. at least my query runs fine...
what hardcoded value are you referring to ? you can always change the 140 / 90 to a variable in the query KH[spoiler]Time is always against us[/spoiler] |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-02-17 : 23:48:20
|
quote: Originally posted by Sonu619 Guys just find out this query is giving right value for meSelectA.*,B.*From TableA AInner Join TableB B On A.ID = B.IDWhere SUBSTRING(BP,1,CHARINDEX('/',BP)-1) = '140'AND LTRIM(RIGHT(BP, CHARINDEX('/', BP + '/')-2) ) = '90'Above query is giving me if one value match giving me record. How i bound it if both condition match then give me resule?
quote: My Requirement IS If BP is <= 140/90.
Your query does not really matches your requirement stated in your earlier post. KH[spoiler]Time is always against us[/spoiler] |
|
|
Sonu619
Posting Yak Master
202 Posts |
Posted - 2013-02-18 : 00:03:52
|
Kh Thank you for your reply. I got the solution and its works fine. If you want i will provide the final query Here? |
|
|
|
|
|
|
|