Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2006
    Posts
    1

    Unanswered: How to retreive Data using Joins

    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;

  2. #2
    Join Date
    Dec 2005
    Location
    Tilburg, Netherlands
    Posts
    73
    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

    > SELECT * FROM users WHERE clue > 0;
    Empty set (0.00 sec)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •