Complex SELECT logic regarding two tables in mysql

0
votes

I have two tables with the following schema

Table1

field1 | field2

Table2

field2 | field3

What I want to do is the following:

Get field2 AND field3 FROM table2 IF THERE ARE NO table1.field2 = table2.field2 AND table1.field = 'x'

In a walkthrough:

Table1

field1 | field2

1 | something

1 | somethingelse

2 | another

Table2

field2 | field3

yetanother | to_get

In this example I want to get 'yetanother' and 'to_get' because there are no fields field2 in table2 that = yetanother and my 'x' in the query is 1. The problem with this is that I will retrieve 2x times the information I want because there are two times the field1 = '1' in table1. So, my query will get yetanother, to_get; yetanother, to_get because of the duplicate. I tried using DISTINCT and LIMIT but that won't work because it is applied to the fields in table2, not in table1. So, if I have 3 rows in table1 with field1 = 1, distinct or limit will "erase" one of them and retrieve two yetanother, to_get; yetanother, to_get. If I have 10 rows with field1 = '1' it will retrieve 9 times yetanother, to_get. If there was a row with "field1 = '1'" and "field2 = 'yetanother'" then I want the query to return nothing (because field2 is in common). How can I achieve this? I don't know if this is actually easy or difficult but I really feel I'm missing and messing something here that I'm forgetting...

2
mysql

2 Answers

0
votes

Try this homie

SELECT DISTINCT *             /* select all */
FROM table1 t1, table2 t2     /* from these 2 tables */
WHERE t1.field2 <> t2.field2  /* where t1 field1 != t2  field1 */
AND t1.field1 = 'x';          /* and t1.field 1 is X */

Let me know if it works out for you.

0
votes

Simply use not exists:

select t2.*
from table2 t2
where not exists (select 1
                  from table1 t1
                  where t1.field2 = t2.field2 and t1.field = 'x'
                 );