| Author |
Topic |
|
DataDave
Starting Member
7 Posts |
 Posted - 2014-07-21 : 12:55:11
|
| HiI am using MS SQL Management Studio to an Azure db. the data is restricted so I cant share it with you.Basically, here is my code and I will explain my problem:SELECT sum([LineSrvcCnt]) FROM [dbo].[PhysicianBilling] left join [dbo].[physicians] on [dbo].[PhysicianBilling].[npi] = [dbo].[physicians].[npi]where [dbo].[PhysicianBilling].[NPI] = '1104890086'when I run just the sum([LineSrvcCnt]) without the join i get 18055, which is the magic number I am looking for. As soon as i do any kind of join, the 18055 comes out a lot higher. In this case, its comes out with 144k, purely because the number of rows in [dbo].[physicians] is 8. So what its doing is muliplying the 18055 by 8. All the rows in the [dbo].[physicians] table are not really needed, I just need it pull through 1 row, so it keeps the 18055.I think that pretty much explains it. So, in essence I need the join but instead of it multiplying it by how many rows are in the [dbo].[physicians] table, which may range from 1 -20, I just need 1 row.Is there any kind of code I can use to suppress the columns from the joined table into 1? or something similar?Thanks in advanceDavid |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-21 : 12:57:12
|
| Why do you need the join at all? I don't see it being used except by the join.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
DataDave
Starting Member
7 Posts |
Posted - 2014-07-21 : 12:59:59
|
| Thanks, There is another table that the joined table links to that the original table doesnt have anything to link to it |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-21 : 13:15:11
|
| Why do you need the other table?Do all 8 rows in physicians table have the same value for that linking column?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
DataDave
Starting Member
7 Posts |
Posted - 2014-07-21 : 13:42:51
|
| some of the rows in that table are slightly different and used for other purposes outside of this. there is one column however that is the same that is needed to link to this outside table |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-21 : 13:54:41
|
| Add a WHERE condition for that one row then.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
DataDave
Starting Member
7 Posts |
Posted - 2014-07-21 : 13:57:27
|
| in principle that would work, but when another "[dbo].[PhysicianBilling].[NPI] = '1104890086'" is used the new WHERE wouldnt be the same as the one i have just used. So i wonder if there is a way to just pick the 1st one out of the list of 8 or how ever many there are.So rather than it multiplying it by 8, it would just use the 1st entry from the joined table, so therefore it would see 1 row instead of 8 for every NPI I select. |
|
|
|
DataDave
Starting Member
7 Posts |
Posted - 2014-07-21 : 14:04:16
|
| can a top 1 be used in a WHERE clause or something similar. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-21 : 14:17:24
|
| If you want ANY top 1, then here you go:SELECT sum([LineSrvcCnt])FROM [dbo].[PhysicianBilling]left join (select TOP 1 * FROM [dbo].[physicians]) p on [dbo].[PhysicianBilling].[npi] = p.[npi]where [dbo].[PhysicianBilling].[NPI] = '1104890086'If you want a specific one, you'll need to show us some sample data. We don't care about your actual data. We need sample data to help explain things since describing isn't always adequate.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
DataDave
Starting Member
7 Posts |
Posted - 2014-07-21 : 14:19:32
|
| Amazing!!! it worked. Thank you so much Tara. Genius!!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Join problem
Tara Kizer