duplicate rows in joins

[sweeny500]

Hi

I may be misunderstanding the concept of joins but what I want to do should be simple but is not working out. I am working on a real estate site where I decide to divide the properties into residential and commercial and put them into seperate tables. The common field is the username. So I would like to select all the details from all the properties from both tables with the same username and displayeach in a table. I works fine when i use 1 table but the join creates a load of duplicates. i.e there are 3 properties in the commercial table and 21 in the residential table so this is giving me 63 results where I would expect 24 (21 + 3)

 

 

Any Help really appreciated

 

This is what I have been playing around with and no luck!

 

 

 

 

//$sqlg="SELECT * FROM res,com WHERE res.g_uname='$_SESSION[user_name]' ORDER by res.g_refreshdate DESC";
	//$sqlg="SELECT * from res join on com using g_uname";
	$sqlg= "select res.g_proptype from res inner join com on res.g_uname ='$_SESSION[user_name]'";
	//$sqlg= "select com.g_proptype from com left join res on com.g_uname = res.g_uname WHERE com.g_uname=res.g_uname";
	//$sqlg= "select res.g_proptype from com left join res on com.g_uname='$_SESSION[user_name]'";
	//$sqlg="Select * from res OUTTER JOIN com USING(g_uname) WHERE res.g_uname='$_SESSION[user_name]'";
	//$sqlg="select r.g_proptype, c.g_uniqueid from res r , com c  where r.g_proptype=c.g_proptype";
	//select a.name as name1, b.name as name2 from table1 as a, table2 as b where condition=condition.
	//SELECT * FROM lt_r5_armor lt INNER JOIN mobs m ON lt.mobid = m.id INNER JOIN arms a ON lt.armsid = a.id 
	//$sqlg="select DISTINCT r.g_proptype, c.g_uniqueid from res r , com c  where r.g_uname=c.g_uname";//works a bit
	//$sqlg="select DISTINCT * from res r , com c  where r.g_uname=c.g_uname";
	//$sqlg="SELECT DISTINCT res* FROM res, com WHERE res.g_uname='$_SESSION[user_name]' AND com.g_uname='$_SESSION[user_name]'";
	//$sqlg="SELECT DISTINCT * FROM res,com WHERE res.g_uname IN (SELECT g_uname FROM com)";

[toplay]

Your post is hard to read...so I'm not looking at it in great detail.

 

When you do joins, you have to tie (specify) both tables together and I don't see you doing that. i.e.:

... ON res.g_uname = com.g_uname WHERE res.g_uname = 'something' ORDER BY ...

 

You also might want to read up about the "GROUP BY" clause.

 

If both tables (residential and commercial) have the same columns, then you can just use a UNION.

 

(SELECT * FROM res WHERE uname = 'username')

UNION

(SELECT * FROM com WHERE uname = 'username')

ORDER BY g_refreshdate DESC

 

That automatically removes any duplicates.

 

FYI: To get duplicates you would use "UNION ALL".

 

 

 

[sweeny500]

Your post is hard to read...so I'm not looking at it in great detail.

 

When you do joins, you have to tie (specify) both tables together and I don't see you doing that. i.e.:

... ON res.g_uname = com.g_uname WHERE res.g_uname = 'something' ORDER BY ...

 

You also might want to read up about the "GROUP BY" clause.

 

If both tables (residential and commercial) have the same columns, then you can just use a UNION.

 

(SELECT * FROM res WHERE uname = 'username')

UNION

(SELECT * FROM com WHERE uname = 'username')

ORDER BY g_refreshdate DESC

 

That automatically removes any duplicates.

 

FYI: To get duplicates you would use "UNION ALL".

 

 

 

[sweeny500]

Hi

Thanks for the quick reply.

Do you think it is advisable to divide the properties into different tables at all or put them in 1 table (and hence no joins needed) with just a flag identifing it as either res or commercial considering there could be as many as 3000 entries. The colums are almost identical.

[toplay]

It depends on a few factors that only you would know.

 

How similar are they or how different can they get in the future?

 

Depends on what's important to you: data normalization, speed, making it easy, maintainability, easy to change for the future, etc.

 

You could have one table and a type designating whether is residential or commercial. But then how can you easily add things to either type as things grow or change?

 

Well, you could go for good data normalization by doing something like this:

 

Have an amenities type table that signifies all the different possible things that a property could have which can be used by both residential and commercial. It just grows over time, but most codes can just be reused over and over.

 

code  Description

pool    Swimming pool

strg    Storage

Shed  Shed

plot    Parking lot

firep  Fireplace

 

Then a table that holds what amenities a particular property has.

 

Property ID    Amenity code

1                  pool

1                  fire

2                  plot

 

The property ID in this case ties back to the main table that has the property info (like your res table). So, the above example shows that property ID 1 has a pool and fireplace, and property 2 has a parking lot (and is probably commercial).

 

The above is just something off the top of my head. A real estate site/application could have lots of similar data, and I would organize the data where I would have little data duplicated or redundant.

 

Logical pieces of data need to be organized in it's own separate table. Joins are used heavily in general and necessary when your tables are normalized properly.

 

 

http://www.datamodel.org/NormalizationRules.html

 

http://www.google.com/search?hl=en&q=db+normalization

 

 

[aschk]

As I believe toplay has suggested, you want to normalise your data first.

There are 2 ways to do it;

the 1st is to create a column for both property types (residential/commercial) and then to tick one or the other depending on which it is, however this will ALWAYS lead to 1 of those two fields being blank. I'm assuming of course that a property CAN'T be both commerical AND residential at the same time.

the 2nd is to have a table called "property_types" and have it hold all the types of property (in case you discover some more). So you have

id | type
============
1  | residential
2  | commerical
3  | derelict

 

then all you need is 1 column in your property table (which contains all the properties) which is a Foreign Key reference (id number) to the property_types table

[sweeny500]

Hi Folks

mmmm! I think I may have to reorganize my database tables and structures as the advice here makes a lot of sense to me now.

Thanks again.