CODEWITHSUNDEEP
mysqlsql
Brad Bird
Brad Bird

Reputation: 737

SQL Specific Statement Query

I am doing quite a large query to a database and for some reason it is returning many results that do not match any of my search terms. It also seems to duplicate my results so I get the same SQL item 16 times. Any ideas why?

SELECT a.*
FROM
  j20e8_datsogallery AS a,
  j20e8_datsogallery_tags AS t
WHERE
  (a.id LIKE "%bear%" OR
   a.imgtitle LIKE "%bear%" OR
   a.imgtext LIKE "%bear%" OR
   a.imgauthor LIKE "%bear%" OR
   t.tag LIKE "%bear%")
ORDER BY a.id DESC
LIMIT 0, 16

I think it maybe something to do with the LIKE %term% section but cannot get it working at all.

Upvotes: 0

Views: 77

Answers (4)

brazilianldsjaguar
brazilianldsjaguar

Reputation: 1449

I'd make sure you qualify your join. Otherwise you'll end up with a full join, or worse, a Cartesian product from a cross join. Something along these lines:

SELECT a.*
FROM
j20e8_datsogallery AS a
JOIN j20e8_datsogallery_tags AS t ON a.ID = t.GalleryID
WHERE
...
ORDER BY a.id DESC
LIMIT 0, 16

Also, consider using a FULLTEXT INDEX ... it could combine all those columns into a single index, and would make searching all of them quite functional.

A FULLTEXT INDEX in MySql can be used to 'combine' several different columns into one big pile of text, which you can then MATCH() columns AGAINST search terms.

To create a FULLTEXT INDEX, you can simply use the CREATE INDEX syntax documented here.

CREATE FULLTEXT INDEX FDX_datsogallery 
ON j20e3_datsogallery ( id, imgtitle, imgtext, imgauthor )

You can then use it in a query with the MATCH() ... AGAINST statements, which are documented here:

SELECT a.*
FROM j20e8_datsogallery AS a
WHERE MATCH( id, imgtitle, imgtext, imgauthor ) AGAINST( 'bear' )

Upvotes: 3

StevieG
StevieG

Reputation: 8709

Your results are a cartesian product of your data, because you don't have a join condition. This means that it is returning every combination of matching rows from a and t.

You probably need to so something like this:

SELECT a.*
FROM
  j20e8_datsogallery AS a,
  INNER JOIN j20e8_datsogallery_tags AS t ON a.id = t.a_id --(or whatever the foreign key is)
WHERE
  (a.id LIKE "%bear%" OR
   a.imgtitle LIKE "%bear%" OR
   a.imgtext LIKE "%bear%" OR
   a.imgauthor LIKE "%bear%" OR
   t.tag LIKE "%bear%")
ORDER BY a.id DESC
LIMIT 0, 16

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

It's bringing back multiples because:

SELECT a.*
FROM j20e8_datsogallery AS a, j20e8_datsogallery_tags AS t

brings back every combination of records from the two tables on it's own. So bear in one table joins to every record in the other table.

You need to specify a relationship between the tables, preferably using an explicit JOIN

Upvotes: 1

Jafar Kofahi
Jafar Kofahi

Reputation: 763

You have a cross join between the two tables, which means every row in a will be joined with every row in t, and as I said in my comment, you will be getting every record that has bear in one of those fields.

you should have a join condition somewhere. Then do your filtering.

Upvotes: 0

Related Questions