How to retreive Data using Joins

Mani_Kanta · #1 (**permalink**) 12-20-06, 01:28

Any one help how retreive data using Joins. When I am using joins it displaying same record twice. b/c in main table it exist one time and in join table it exist two time. I am used Left Join to join the table. When I am using inner joins it didn't display any records.

This is the query I am used:

$sql= "SELECT p.*, a.id AS agentid, a.name AS agent, a.user AS user, c.name AS company, c.id AS companyid, t.name AS type, p.type AS typeid "
. "\n FROM #__hp_companies AS c , ((((#__hp_properties AS p INNER JOIN #__hp_agents AS a ON p.agent = a.id) INNER JOIN"
. "\n #__hp_prop_types AS t ON p.type = t.id) INNER JOIN #__hp_properties2 AS p2 ON p.id=p2.property) INNER JOIN "
. "\n #__hp_featured AS f ON p2.property=f.property ) INNER JOIN #__hp_prop_ef as ef ON p2.field=ef.id"
. "\n WHERE p.published='1' AND p.approved='1' AND t.published='1'"
. "\n AND (publish_up = '0000-00-00 00:00:00' OR publish_up <= NOW())"
. "\n AND (publish_down = '0000-00-00 00:00:00' OR publish_down >= NOW())"
.(count( $where ) ? " AND " . implode( ' AND ', $where ) : "")
.(($which == "featured") ? "\nAND p.featured='1'" : "")
. "\nORDER BY ".(($which == "feature d") ? "f.ordering ASC" : "p.".$sort." ".$order)
. "\nLIMIT ".$limitstart.", ".$limit;

Yveau01 · #2 (**permalink**) 12-21-06, 03:06

Make sure you include all the PK fields of the tables in your join clauses. Otherwise you end up with records being mentioned twice in the result set. Pretty sure that causes it.

SO give us some more info on the tables and we might be able to help you out a bit more ... and readable code snippets might also work in your advantage

Try:

Code:

SELECT  p.*
,       a.id AS agentid
,       a.name AS agent
,       a.user AS user
,       c.name AS company
,       c.id AS companyid
,       t.name AS type
,       p.type AS typeid
FROM    #__hp_companies AS c 
,       ((((#__hp_properties AS p INNER JOIN #__hp_agents AS a ON p.agent = a.id) 
	INNER   JOIN #__hp_prop_types AS t ON p.type = t.id) 
	INNER   JOIN #__hp_properties2 AS p2 ON p.id=p2.property) 
	INNER   JOIN #__hp_featured AS f ON p2.property=f.property ) 
	INNER   JOIN #__hp_prop_ef as ef ON p2.field=ef.id
WHERE   p.published='1' 
AND     p.approved='1' 
AND     t.published='1'
AND     (publish_up = '0000-00-00 00:00:00'
        OR publish_up <= NOW())
AND     (publish_down = '0000-00-00 00:00:00'
        OR publish_down >= NOW())
...

Also there is a bit of PHP code at the end of the SQL statement, I did not include it here. But try to write 'steady SQL code' that is not altered by PHP or whatever. Makes life a lot easier. There are other ways to solve the dependencies of the SQL statements. That will look a lot more tempting to a DB specialist ...

Gr,
Yveau

Internet Services

Web Development

Digital Marketing

Consumer Tech