Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Join Query Issue

Posted on 2011-02-18
11
817 Views
Last Modified: 2012-08-14
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
Comment
Question by:chokka
  • 6
  • 3
  • 2
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34927634
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
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 250 total points
ID: 34927648
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
 

Author Comment

by:chokka
ID: 34927674
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:chokka
ID: 34927734
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 34927744
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
 

Author Comment

by:chokka
ID: 34927766
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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34927804
SELECT * FROM vw_Test3 VWT WHERE NDC NOT IN (SELECT Left(NDC,11) FROM [340BPrice])

What is the result of this query ?
0
 

Author Comment

by:chokka
ID: 34927845
10 Rows affected -
0
 

Author Comment

by:chokka
ID: 34927854
So, Both Angel and Rajkumar Query are results same number of rows affected.
0
 

Author Comment

by:chokka
ID: 34927857
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34932416
my query will show those rows from vw_Test3 VWT  where there is no matching row in [340BPrice]
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question