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
RIASAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Use alias..

Select DISTINCt b.Ref  FROM  
table2 b LEFT JOIN table1 a ON a.Column1 = b.Column1
WHERE a.Column1 = 'Yes'
0
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
RIASAuthor Commented:
Ryan your query did not return any value
0
 
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
 
RIASAuthor Commented:
Thanks Pawan!
Thanks Ryan as well!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.