Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 830
  • Last Modified:

Join Query Issue

SQL 2008

For every NDC, I need matching Packet Size / Price.

PacketSize and Price, i am pickingfrom different Table.

When i execute Inner Join, i am getting 341 Rows Affected.

When i execute Left Join, I am getting 351 Rows Affected.

Actually, i expect 349 Rows Affected. Since, if i am not using any Join and executing the parent table :  vw_Test3  , i am getting 349 Rows Affected.

So i am confused.
select VWT.[Generic Code],  
  VWT.NDC,  
  VWT.[Drug Name],  
  VWT.Qty,  
  BP.PacketSize,  
  BP.Price from vw_Test3 VWT  
Inner join [340BPrice] BP  
on  
VWT.NDC = Left(BP.NDC,11)  


select VWT.[Generic Code],  
  VWT.NDC,  
  VWT.[Drug Name],  
  VWT.Qty,  
  BP.PacketSize,  
  BP.Price from vw_Test3 VWT  
Left join [340BPrice] BP  
on  
VWT.NDC = Left(BP.NDC,11)

Open in new window

0
chokka
Asked:
chokka
  • 6
  • 3
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the problem is described in this article:
http://www.experts-exchange.com/A_3203.html
you can even find your (sql) solution there.

in short:
* with inner join, you get less rows because there are a couple of rows where there is no matching row in the joined table
* with left join, you get more rows because for at least 1 row, you have more than 1 matching row in the joined table.

so: you need to find out which row you want from the joined table, in those cases.

please double-check your data.
0
 
Rajkumar GsSoftware EngineerCommented:
If you use INNER JOIN, result will contain only those records that are contain in both tables based on the joining column.
If you use LEFT JOIN, all the records from the left side table will be returned - Plus - all related records from right side table. For eg:- If for a record in main table there are two record in sub table, the result with LEFT JOIN will contain two records

This is happening in your case
0
 
chokkaAuthor Commented:
In my scenario as experts what you recommend me to go either Inner or Left.

And also, is there any way for me to find out missing NDC 's which is not matched with Join Table - 340BPrice.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
chokkaAuthor Commented:
When i run

select * from vw_Test3 VWT  

I am getting 349 Records.

After using Inner Join - 341 Records

How should i identify this missing 8 records.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this will do:
select VWT.[Generic Code],  
  VWT.NDC,  
  VWT.[Drug Name],  
  VWT.Qty,  
  BP.PacketSize,  
  BP.Price from vw_Test3 VWT  
Left join [340BPrice] BP  
on  VWT.NDC = Left(BP.NDC,11)
WHERE  BP.NDC IS NULL

Open in new window

0
 
chokkaAuthor Commented:
Angel :- That query returns 10 rows.

What does this query actual doing ? I am sorry, i am not able to get the exact point.
0
 
Rajkumar GsSoftware EngineerCommented:
SELECT * FROM vw_Test3 VWT WHERE NDC NOT IN (SELECT Left(NDC,11) FROM [340BPrice])

What is the result of this query ?
0
 
chokkaAuthor Commented:
10 Rows affected -
0
 
chokkaAuthor Commented:
So, Both Angel and Rajkumar Query are results same number of rows affected.
0
 
chokkaAuthor Commented:
Same number of rows and same records.

So, my understanding is

1) I have to use Inner Join for matching records
2) I have to use any of these queries for missing price records
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
my query will show those rows from vw_Test3 VWT  where there is no matching row in [340BPrice]
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now