MySQL INNER JOIN without duplicates-CodePudding

I have two tables. One of them has duplicates within its IDs, the other table is unique. I want to inner join only with the id with content.

Sample for table textinfo

ID	Content
1	aaaa
1
2	bbbb
2
3	cccc
3

SELECT * 
FROM infotext a 
INNER JOIN article b ON a.ID = b.InfotextID 
AND a.Content <> ''

I know this cannot work, but I don't know how to query only for the content rows.

The result after this query has a lot of duplicates in the articles with different contents.

CodePudding user response：

You can check whether the empty rows are null or '', trimming white characters:

SELECT * FROM infotext a INNER JOIN article b ON a.ID = b.InfotextID AND TRIM(COALESCE(a.Content, '')) <> ''

CodePudding user response：

You can try also :

SELECT DISTINCT * FROM infotext a INNER JOIN article b ON a.ID = b.InfotextID AND a.Content <> '' GROUP BY b.InfotextID

CodePudding user response：

You can try the distinct method

SELECT DISTINCT * 
FROM infotext a 
INNER JOIN article b ON a.ID = b.InfotextID 
AND a.Content <> ''

CodePudding user response：

Use EXISTS:

SELECT i.*
FROM infotext i
WHERE EXISTS ( SELECT NULL
               FROM article a
               WHERE a.InfotextID = i.id
             )

You could add TRIM(a.Content) <>'' even though is is not needed.

SELECT i.*
FROM infotext i
WHERE EXISTS ( SELECT NULL
               FROM article a
               WHERE a.InfotextID = i.id and TRIM(a.Content) <>''
             )

Note that I changed infotext alias to i and article to a

Check the difference between using your query and EXISTS on the fiddle