CODEWITHSUNDEEP
mysqlsql
user3485007
user3485007

Reputation: 51

mysql select information from two tables if second has match

I got two tables. not every row connects with table2 but if they do id, supplier are the same. i want to select all records from table1 with query LIKE or match against, but it will only select if the suppliers are in table2 too. Any ideas?

table1
id          | 1
supplier    | 1

extra information table:

table2
id        | 1
supplier  | 1
extrainfo | "Best information"

query

SELECT
    table1.*,table2.*
FROM
    mytable_normalinfo as table1,
    mytable_extrainfo as table2
WHERE 
  MATCH(table1.fulltextcollum)
  AGAINST ('+test*' IN BOOLEAN MODE)
AND
     table1.supplier =  table2.supplier 
AND
     table1.id = table2.id
LIMIT 10;

Upvotes: 0

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271031

I think you just want a left join:

SELECT table1.*, table2.*
FROM mytable_normalinfo as table1 LEFT JOIN
     mytable_extrainfo as table2
     ON table1.supplier =  table2.supplier AND
        table1.id = table2.id
WHERE MATCH(table1.fulltextcollum) AGAINST ('+test*' IN BOOLEAN MODE)
LIMIT 10;

Two notes:

  • Learn to use proper JOIN syntax. Simple rule: Never use commas in the FROM clause.
  • When you use LIMIT, normally you would also use ORDER BY.

Upvotes: 4

Related Questions