0

I'd like to 

SELECT DISTINCT column1, column2 as t1

And i'd like to 

SELECT column3 FROM table2 WHERE column4 = "1" AND column3 = (matches)t1.column1

How should my query look like?

SELECT DISTINCT column1, column2 FROM table1 as t1,
column3 FROM table2 WHERE column4 = "1" AND column3 = t1.column1

The result should be a list with data from column1, that matches data from column3 AND column4 = 1

Table1:                Table2:
-----------------------------------------
column1    column2     column3    column4
-----------------------------------------
001        Name        001        1      
002        Name        002        0
003        Name        004        1
005        Name        005        1
-----------------------------------------
Result: 001, 005

Error:

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM table2 WHERE column4 = '1' AND column3 = t1.column1' at line 1

Lelio Faieta
  • 6,457
  • 7
  • 40
  • 74
Björn C
  • 3,860
  • 10
  • 46
  • 85

2 Answers2

2
select distinct
   t1.column1,
   t1.column2,
   t2.column4
from
   table1 as t1
inner join
   table2 as t2 on t2.column3 = t1.column1
where
   column4 = "1"
Alexey
  • 2,388
  • 1
  • 16
  • 32
0

Your query should look like this:

SELECT DISTINCT
    t1.column1, t1.column2, t2.column3, t2.column4
FROM
    table1 AS t1,
    table2 AS t2
WHERE
    t2.column4 = '1'
        AND t2.column3 = t1.column1
PST
  • 346
  • 2
  • 5
  • 1
    It is not recommended to use this notation. A proper join should be used. – Linkan Dec 22 '16 at 09:54
  • http://stackoverflow.com/questions/2241991/in-mysql-queries-why-use-join-instead-of-where This notation is old and depricated. – Linkan Dec 22 '16 at 09:57
  • 1
    This notation is called implicit join. It has been deprecated for long compared to the explicit join that allows you to do more than just an inner join – Lelio Faieta Dec 22 '16 at 10:01