[RESOLVED] Will a Join Work?

whodigs

I am trying to make a query for a search result. Basically I need to search for properties by apartment community and also by the floorplans themselves. I am trying to use a join to accomplish this with this code:

SELECT a.*, b.* FROM ((SELECT * FROM properties WHERE Active = '1') AS a LEFT JOIN (SELECT * FROM floorplans WHERE Size = '1BD%' OR Size = '2BD%') AS b ON (a.PropID=b.PropID)) LIMIT 30

Basically I am trying to have a page of checkboxes with the various options and then just create a variable for each one if it contains a value. The problem I am having now is my join is creating values of NULL in the floorplans columns on the join.

Any help would be greatly appreciated.

alimadzi

I doubt you need multiple subqueries to accomplish this. Since your query is very hard to follow when it's all on one line in a scrolling box, why don't you post it again with some line breaks and I'll take another look.

whodigs

SELECT a.*, b.* FROM 
(
(SELECT PropID, Active FROM properties WHERE Active = '1') 
AS a LEFT JOIN 
(SELECT Size FROM floorplans WHERE Size = '1BD%' OR Size = '2BD%') 
AS b ON 
(a.PropID=b.PropID)
) 
LIMIT 30

Hopefully that helps.

alimadzi

Much better. Let me take a crack at rewriting this without subqueries...

SELECT p.PropID, p.Active, f.Size
  FROM properties
  LEFT JOIN floorplans f ON p.PropID = f.PropID
  WHERE
    p.Active = '1' AND
    (f.Size LIKE '1BD%' OR f.Size LIKE '2BD%')
  LIMIT 30

That should work for you. Your other query probably would have worked too except you needed to use LIKE with the wildcards in your WHERE clause. That's fixed now and I removed your two subqueries too.

Let me know if this works for you.

whodigs

Appreciate your help. This query kicked back an error for me:

#1054 - Unknown column 'p.PropID' in 'field list'

Do the properties and floorplans need to be defined as p and f like I did in the previous attempt at this?

whodigs

never mind i got it working i appreciate the help

alimadzi

whodigs wrote:
never mind i got it working i appreciate the help

Glad to help. Subqueries have their place, but should be avoided if possible because they can be very resource-intensive. Best of luck with the rest of your app!