JoeP JoeP - 1 month ago 6
MySQL Question

MySql SELECT from table1 if column in table2 = 1

I need to return a column from

table1
if a column in
table2
, which has the same identifier with a different column name, has a
1
in a specific field.

table1

table1

table2

table2

In this example I would like to retrieve
item_code
222
from
table1
where
alt1
is
888
, but only if
alt1
is
1
for
item
in
table2
.

I've come to a point where I'm thinking it's actually impossible, I've tried numerous inline queries and
LEFT JOIN
variants but nothing seems to be able to actually perform the task. Writing out all the versions I've tried is pointless as none of them have worked, but an example would be:

SELECT item_code FROM table1 WHERE alt1 = "888"
AND (SELECT item FROM table2 WHERE item = "222" AND alt1 = 1)


The above is probably not the best example of something I've tried, but since none of them have worked so far it doesn't really matter. Any help is greatly appreciated.

Answer

Try this

SELECT item_code from table1 INNER JOIN 
table2
ON table1.item_code = table2.item where table2.alt1 = 1 and table1.alt1 = 888

Hope this works.