Select from two tables, one column

Question

Please help me to write a Select :)

I need to return data from two tables: values from table X, but only these which also have value from table Y. For example in table X values are:

Column A	Column B	----
Great	Orange	<- has attached photo which is located in table Y
Poor	Orange
Poor	Apple	<- has attached photo which is located in table Y
Awesome	Orange	<- has attached photo which is located in table Y

I need to return values column A from table X, where value in column B is 'Orange' and only those which has attached photo in table Y.

Table X is in connection with table Y: referencing constrains

Name	Table	Table owner	Unique	Columns
Table_X	Table_Y	DBA	No	Table_X_id

I tried this select, but it does not work properly, because it returns value 1 in every line where value is orange:

SELECT * FROM Table_X WHERE Table_X.Column_B='Orange' AND (EXISTS (select 1 from Table_Y att, Table_X orng where orng.Table_X_id=att.Table_X_id and att.Table_X_id is not null))

I hope it's clear.. help me :)

Answer 1

Did you try this?

Select x.ColumnA from Table_X x
left join Table_Y y on y.Table_X_id = x.Table_X_id
where x.ColumnB = 'Orange' and and y.Att is not null

Answer 2

I would suggest a little change to the previously suggested query, if you need only the ones that appear on table Y, it should be an inner join:

SELECT Table_X.Column_A 
FROM Table_X 
INNER JOIN Table_Y
ON Table_X.Table_X_id=Table_Y.Table_X_id
WHERE Table_X.Column_B='Orange'