Solved

# Selection from table2 where criteria for table1

Posted on 2016-11-07
18 Views
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
Question by:RIAS
• 5
• 3
• 2

LVL 16

Expert Comment

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'
``````

or

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

LVL 49

Expert Comment

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
``````
0

Author Comment

Ryan your query did not return any value
0

Author Comment

Pawan,
is there any way I can find distinct values from table2 based on the selection.
0

LVL 49

Expert Comment

@RIAS,

pls provide sample data so we can provide proper solution
0

Author Comment

Pawan's query worked!
0

LVL 16

Expert Comment

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

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

Pawan Kumar Khowal earned 500 total points
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

Thanks Pawan!
Thanks Ryan as well!
0

## Featured Post

### Suggested Solutions

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.