[Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Selection from table2 where criteria for table1

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
RIAS
Asked:
RIAS
  • 5
  • 3
  • 2
1 Solution
 
Pawan KumarDatabase ExpertCommented:
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
 
Ryan ChongCommented:
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
 
RIASAuthor Commented:
Ryan your query did not return any value
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
RIASAuthor Commented:
Pawan,
is there any way I can find distinct values from table2 based on the selection.
0
 
Ryan ChongCommented:
@RIAS,

pls provide sample data so we can provide proper solution
0
 
RIASAuthor Commented:
Pawan's query worked!
0
 
Pawan KumarDatabase ExpertCommented:
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
 
RIASAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Use alias..

Select DISTINCt b.Ref  FROM  
table2 b LEFT JOIN table1 a ON a.Column1 = b.Column1
WHERE a.Column1 = 'Yes'
0
 
RIASAuthor Commented:
Thanks Pawan!
Thanks Ryan as well!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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