[RESOLVED] Not getting all results

[Dillenger]

Hello I cannot seem to get all results with my query... I have 2 rows that have the same info, only 1 has a link to menuitems, the one with no link to menuitems does not show up and no matter what I try I can't get the query to show both:

SELECT listings.*, menuitems.menuName, menuitems.menuDescription

,(((acos(sin((-123.450461*pi()/180)) * sin((listinglat*pi()/180))+cos((-123.450461*pi()/180)) * cos((listinglat*pi()/180)) * cos(((48.579731- listinglong)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance 

,((1.3*(MATCH (listingName) AGAINST ('(bubby) ("bubby") (+bubby)' IN BOOLEAN MODE))) + (0.6*(MATCH (listingDescription,listingAddress1,listingAddress2,listingPostalCode,listingCity,listingProvince,listingPhone,listingFax,listingEmail,listingWebsite,listingLocation,menuName,menuDescription) AGAINST ('(bubby) ("bubby") (+bubby)' IN BOOLEAN MODE)))) AS relevance 

FROM listings,menuitems WHERE 

(MATCH (listingName,listingDescription,listingAddress1,listingAddress2,listingPostalCode,listingCity,listingProvince,listingPhone,listingFax,listingEmail,listingWebsite,listingLocation,menuName,menuDescription) AGAINST ('(bubby) ("bubby") (+bubby)' IN BOOLEAN MODE)) 

AND menuitems.listingID = listings.listingID 
AND listings.listingActive = 0 
GROUP BY listingID ORDER BY relevance DESC

I know its because of this line, that the second record isn't showing up but I can't seem to alter it so that it does. Any help would be greatly appreciated

AND menuitems.listingID = listings.listingID 

[Piranha]

You should look into joins. Let's take an example. You have two tables:

[b]Table person[/b]           

[b]id           name[/b]
1            Anders
2            Bertil
3            Cesar

[b]Table phone[/b]           

[b]id               phone               personid[/b]
1                1                   1
2                2                   2
3                3                   2
4                4                   4

A person may have zero or more phone numbers. Person 1 (Anders) have one phone number, person 2 (Bertil) have two phone numbers and person 3 (Cesar) doen't have any phone number. There also is a phone number that doesn't belong to a person. The way you are joining is the same as an inner join, or a join. The 3 queries below will return the same result:

select * from person, phone where person.id = phone.personid

select * from person join phone on person.id = phone.personid

select * from person inner join phone on person.id = phone.personid

They all will return person 1 and person 2 with their phone numbers. Because person 3 doesn't have any phone number he won't be returned. With the help of different ways to handle joins you can return different results:

select * from person left join phone on person.id = phone.personid

select * from person right join phone on person.id = phone.personid

select * from person left outer join phone on person.id = phone.personid

select * from person right outer join phone on person.id = phone.personid

select * from person outer join phone on person.id = phone.personid

select * from person full outer join phone on person.id = phone.personid

Test it out and look at the different results. And try to learn to use the different ways to join tables with the keyword "join" instead of placing the tables after "from". It will cause troubles in certain cases, using "join" never causes trouble (to my knowledge).

[edit]Changed to "on" instead of "where" with join. Ty Scooter for pointing out my mistake.[/edit]

[Sxooter]

Just a quick note, joins use an on clause, not a where clause:

select * from tablea join tableb on (tablea.field=tableb.field) where tablea.id in (1,2,3,4);

[Dillenger]

Thank you for your help. I've modified the Query a little bit, but still having some issues. It now retreives the second result, but it's also retreiving all results where the masterID is set. I'm not sure how to filter out the ones I don't need. The query also took about 20 sec. which isn't very fast. None of the joins seem to work for me except inner join, maybe that's the problem?

Either that, or maybe I need to join with the menuitems.listingID too?

SELECT listings.*, menuitems.menuName, menuitems.menuDescription
,(((acos(sin((-123.450461*pi()/180)) * sin((listinglat*pi()/180))+cos((-123.450461*pi()/180)) * cos((listinglat*pi()/180)) * cos(((48.579731- listinglong)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance 
,((1.3*(MATCH (listingName) AGAINST ('(bubby) ("bubby") (+bubby)' IN BOOLEAN MODE))) + (0.6*(MATCH (listingDescription,listingAddress1,listingAddress2,listingPostalCode,listingCity,listingProvince,listingPhone,listingFax,listingEmail,listingWebsite,listingLocation,menuName,menuDescription) AGAINST ('(bubby) ("bubby") (+bubby)' IN BOOLEAN MODE)))) AS relevance 
FROM listings inner join menuitems 
WHERE (MATCH (listingName,listingDescription,listingAddress1,listingAddress2,listingPostalCode,listingCity,listingProvince,listingPhone,listingFax,listingEmail,listingWebsite,listingLocation,menuName,menuDescription) AGAINST ('(bubby) ("bubby") (+bubby)' IN BOOLEAN MODE)) 
AND menuitems.listingID = listings.listingID 
OR menuitems.listingID = listings.masterID 
AND listings.listingActive = 0 
GROUP BY listingID 
ORDER BY relevance DESC

Here are the results, it shouldn't be showing Villages Pizza or Smitty's:

listingID    masterID       listingName                menuName	

197          197             Bubby Rose's Bakery       Challah 	
198          197 	     Bubby Rose's Bakery       Challah 	
14           14              Villages Pizza            VILLAGES SPECIAL 	
35           14              Villages Pizza            VILLAGES SPECIAL 	
47           47              Smitty's                  Eggs Benedict
93           47              Smitty's                  Eggs Benedict

[Piranha]

After Sxooters post I changed the queries, I made a mistake in them. That mistake basically f*cks the whole query up. Please try it again with the correct way to use joins.

[Dillenger]

Still no luck maybe I'm calling it wrong? Here are the things I've tried with the errors below:

FROM listings join menuitems on (menuitems.listingsID = listings.listingID)
Error - Unknown column 'menuitems.listingsID' in 'on clause'

FROM listings full join menuitems on (menuitems.listingsID = listings.listingID)
Error - Unknown table 'listings'

FROM listings left join menuitems on (menuitems.listingsID = listings.listingID)
Error - Unknown column 'menuitems.listingsID' in 'on clause'

FROM listings right join menuitems on (menuitems.listingsID = listings.listingID)
Error - Unknown column 'menuitems.listingsID' in 'on clause'

FROM listings inner join menuitems on (menuitems.listingsID = listings.listingID)
Error - Unknown column 'menuitems.listingsID' in 'on clause'

FROM listings outter join menuitems on (menuitems.listingsID = listings.listingID)
Error - Unknown table 'listings'

[Sxooter]

Dillenger wrote:

Still no luck maybe I'm calling it wrong? Here are the things I've tried with the errors below:

FROM listings join menuitems on (menuitems.listingsID = listings.listingID)
Error - Unknown column 'menuitems.listingsID' in 'on clause'

Pretty sure it's just listingID, not listingsID

[Dillenger]

Ok I am getting both results now, the only problem is the Query took 2:06 seconds to load. How come?

FROM listings inner join menuitems on (menuitems.listingID = listings.listingID OR menuitems.listingID = listings.masterID) 

SELECT listings.*, menuitems.menuID, menuitems.menuName, menuitems.menuDescription,(((acos(sin((-123.450461*pi()/180)) * sin((listinglat*pi()/180))+cos((-123.450461*pi()/180)) * cos((listinglat*pi()/180)) * cos(((48.579731- listinglong)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance ,((1.3*(MATCH (listingName) AGAINST ('(bubby) ("bubby") (+bubby)' IN BOOLEAN MODE))) + (0.6*(MATCH (listingDescription,listingAddress1,listingAddress2,listingPostalCode,listingCity,listingProvince,listingPhone,listingFax,listingEmail,listingWebsite,listingLocation,menuName,menuDescription) AGAINST ('(bubby) ("bubby") (+bubby)' IN BOOLEAN MODE)))) AS relevance FROM listings inner join menuitems on (menuitems.listingID = listings.listingID OR menuitems.listingID = listings.masterID) WHERE (MATCH (listingName,listingDescription,listingAddress1,listingAddress2,listingPostalCode,listingCity,listingProvince,listingPhone,listingFax,listingEmail,listingWebsite,listingLocation,menuName,menuDescription) AGAINST ('(bubby) ("bubby") (+bubby)' IN BOOLEAN MODE)) AND listings.listingActive = 0 GROUP BY listingID ORDER BY relevance DESC

[Piranha]

I would guess that you haven't set the right indexes in the database. Check that you have indexes on all the columns that joins to other tables.

Also check that you have fulltext index in the tables where you use fulltext searches.

Finally you are doing quite a lot of mathematics, and a relevance search. If you return lots of rows you have to do this for each row, and it might take time. I haven't used either that much mathematics or relevance in sql, I don't know how big impact it has.

[Dillenger]

GREAT! i indexed masterID and it works flawlessly now... quick question...

should I be indexing listingID only on the main listings table, or on all tables that it appears as a reference?

[Dillenger]

Hmmm, so that worked great on my localhost, but when I put it live, it's erroring out and then I get a message 'Server shutdown in progress', any ideas?

[Dillenger]

nevermind, had to fix some more indexes