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 :)
CodePudding user response：
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
CodePudding user response：
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'