How to avoid a cartesian product in an INNER JOIN query?

I have 6 tables, let's call them a, b, c, d, e, f. Now I want to search all the columns (except the ID columns) of all tables for a specific word, let it say "Joe". I did this, I did INNER JOINS across all tables, and then used LIKE to search for columns.

INNER JOIN
...
ON
INNER JOIN
...
ON.......etc.
WHERE a.firstname 
~* 'Joe' 
OR a.lastname 
~* 'Joe' 
OR b.favorite_food 
~* 'Joe'
OR c.job
~* 'Joe'.......etc.

The results are correct, I get all the columns I need. But I also have some kind of Cartesian product, I get two or more rows with almost the same results.

How can i avoid this? I want each row to be only once, as the results should appear in a web search.

UPDATE

, SELECT DISTINCT : pastie.org/970959 . ?

+3
5

SELECT DISTINCT?

+2

JOIN tables? foreign keys - ?

, ?

+2

? Microsoft SQL Server ( , - ), .

UNION .

+1

, , , " ". , , , , . - :

SELECT a.*, b.*
FROM (SELECT DISTINCT a.ID
      FROM ...
      INNER JOIN ...
      INNER JOIN ...
      WHERE ...) x
INNER JOIN a ON x.ID = a.ID
INNER JOIN b ON x.ID = b.ID

, , :

  • sloooow, , , ( RDBMS ).

  • , , , OR.

0

, a b , , , ( ).

, , , , . ? - . , "", , "" , , " ". , .

, . " ", (PK) , , , . :

EntityType, EntityID, Keywords
------------------------------
Person,     4,        'Joe', 'Doe'
Company,    12,       'Joe' Gym', 'Gym'

- ?

, , , Person, , ( ). , , , , . .

, , . , .

0
source

Source: https://habr.com/ru/post/1746342/


All Articles