MYSQL LEFT JOIN returns a duplicate table

Question

Refresh Scroll down to sql for sql code and output. Recently, I started working on an apartment database. The database contains 2 tables

The Property table stores information about the main apartment complexes (contact details and photos). This is referred to by the propid column propid

Table b “Floor Plans” stores information about individual rental units. price information, square meters and type of apartment. The propid column is also referenced in the floorplan table so we can link the two tables together. It also has its own unique identifier, but it is not used.

Usually for each complex usually 5-10 floor plans.

I am creating an interface for our customers on our website. I need the ability to search for properties with plans that match my search criteria. but when executing the connection statement, I noticed that he always gave me a list with the same property in it every time it was found that one of its stages matches the search criteria.

I did some research on this. The most common answer uses SELECT DISTINCT.

The problem is that I need more than just insightful, which will be returned as a result.

I tried to do something like this:

 SELECT DISTINCT (p.propid), p.*, f.* FROM property AS p LEFT JOIN floorplans AS f ON p.propid = f.propid WHERE f.pricespecial BETWEEN [min_price] AND [max_price]; 

[min_price] and [max_price] provided by the user.

The intended result is a list of all properties that have floor plans matching all user search criteria. But I do not want the same property to be returned for each matching unit.

When I run this query, I still get duplicate properties

In the past, I simply ran the script filter in the root XML containing the data. The script will determine the highest and lowest price units in the givin property and add these values ​​to 2 newly created columns in the price_min and price_max property table. so far, this has been good enough, but the company has achieved more accurate search results.

The only other option that I see is to simply run the query with only returning DISTINCT. Then run the second query to get the actual data.

those.

 $sql = "SELECT DISTINCT p.propid FROM property as f" . "LEFT JOIN floorplans AS f" . "WHERE f.price BETWEEN " . $_REQUEST['price_min'] . " AND " . $_REQUEST['price_max'] . " 

EDIT * *

some sample output using the new fixed sql state.

 propid name pricespecial 4230A 2222 Smith Street $1225-1450 4230A 2222 Smith Street $1895-2045 4230A 2222 Smith Street $2220 4679A City Place Midtown $1230-1599 4679A City Place Midtown $1595-1650 4679A City Place Midtown $1699-2195 4572A Gables Memorial Hills $1308-2159 4572A Gables Memorial Hills $2050-2693 4606A Venue Museum District $1535-1930 4606A Venue Museum District $1980-2550 

I had problems posting this comment, so I just edited my question.

Answer 1

You get these duplicates because you ask for them.

Do you want to

  SELECT DISTINCT p.propid, p.* FROM property AS p LEFT JOIN floorplans AS f ON p.propid = f.propid WHERE f.price BETWEEN [min_price] AND [max_price] 

When you say SELECT ..., f.* , You say return me a line for each separate combination of property and floorplan . But you only need separate elements of the property .

In general, it is a good idea to indicate the specific columns that you want. Avoid p.* To extract all columns. This is especially true if you are using SELECT DISTINCT .

Answer 2

I like it better, without clear:

 SELECT p.* FROM property AS p WHERE EXISTS (SELECT NULL FROM floorplans AS f WHERE f.propid = p.propid AND f.price BETWEEN [min_price] AND [max_price])