Solved

Selection from table2 where criteria for table1

Posted on 2016-11-07
10
18 Views
Last Modified: 2016-11-07
Hello,

I need to select all refno in table2 where column1 from table1 ='yes'



table1:
Ref --Column1
Auth --Column2

table2:
Ref --Column1
Auth --Column2


Select * from table2 where table1 . auth ='yes'

Any suggestions?

Cheers
0
Comment
Question by:RIAS
  • 5
  • 3
  • 2
10 Comments
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Try this.. I have used left join , you can define that based on your requirement.

SELECT * FROM 
table2 b LEFT JOIN table1 a ON a.Column1 = b.Column1
WHERE a.Column1 = 'Yes'

Open in new window


or

SELECT * FROM 
table2 b LEFT JOIN table1 a ON a.Column1 = b.Column1
AND a.Column1 = 'Yes'

Open in new window

0
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
I would prefer to use inner join like this way:
select b.Ref
from table2 b
inner join table1 a 
on b.Ref = a.Ref
where a.Auth = 'Yes'
group by b.Ref

Open in new window

0
 

Author Comment

by:RIAS
Comment Utility
Ryan your query did not return any value
0
 

Author Comment

by:RIAS
Comment Utility
Pawan,
is there any way I can find distinct values from table2 based on the selection.
0
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
@RIAS,

pls provide sample data so we can provide proper solution
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:RIAS
Comment Utility
Pawan's query worked!
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Use Distinct... Here you can use * for all columns or column name like ref..

Select DISTINCT  *  FROM  
table2 b LEFT JOIN table1 a ON a.Column1 = b.Column1
WHERE a.Column1 = 'Yes'

Select DISTINCT Ref  FROM  
table2 b LEFT JOIN table1 a ON a.Column1 = b.Column1
WHERE a.Column1 = 'Yes'

OR

SELECT DISTINCT * FROM
table2 b LEFT JOIN table1 a ON a.Column1 = b.Column1
AND a.Column1 = 'Yes'
0
 

Author Comment

by:RIAS
Comment Utility
Select DISTINCt Ref  FROM  
table2 b LEFT JOIN table1 a ON a.Column1 = b.Column1
WHERE a.Column1 = 'Yes'

errors as it does not know which table it belongs to
0
 
LVL 16

Accepted Solution

by:
Pawan Kumar Khowal earned 500 total points
Comment Utility
Use alias..

Select DISTINCt b.Ref  FROM  
table2 b LEFT JOIN table1 a ON a.Column1 = b.Column1
WHERE a.Column1 = 'Yes'
0
 

Author Closing Comment

by:RIAS
Comment Utility
Thanks Pawan!
Thanks Ryan as well!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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 …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now