Solved

Selection from table2 where criteria for table1

Posted on 2016-11-07
10
32 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 24

Expert Comment

by:Pawan Kumar
ID: 41876755
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
ID: 41876760
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
ID: 41876764
Ryan your query did not return any value
0
 

Author Comment

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

Expert Comment

by:Ryan Chong
ID: 41876768
@RIAS,

pls provide sample data so we can provide proper solution
0
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.

 

Author Comment

by:RIAS
ID: 41876770
Pawan's query worked!
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41876771
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
ID: 41876774
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 24

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41876775
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
ID: 41876776
Thanks Pawan!
Thanks Ryan as well!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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, f…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

13 Experts available now in Live!

Get 1:1 Help Now