More rows from join clauses

Orc · 07-30-2008, 09:06 AM

How can I get all the rows from a join clause? instead of 1?

Aaron · 07-30-2008, 12:39 PM

PHP Code:


			
$query = "SELECT user.name, message.to".

"FROM user, message";

// I wonder if this would work... LIMIT count(*);

Aaron · 07-30-2008, 12:40 PM

PHP Code:


			
$query = "SELECT user.name, message.to".

"FROM user, message";

// I wonder if this would work... LIMIT count(*);

Try that. I really don't use joins that much. The more queries the better.

CoryMathews · 07-30-2008, 01:35 PM

By default you should always get more then 1 if there is more then one. As long as you dont have a limit 1 on the end of the query. and aaron isnt that method really slow? (no backing on that statement)

Aaron · 07-30-2008, 06:07 PM

Count is extremely slow. That was just something I was wondering about (it had no point whatsoever)

The only thing about my query was not using WHERE.

Orc · 07-30-2008, 08:31 PM

Quote:

Originally Posted by CoryMathews

By default you should always get more then 1 if there is more then one. As long as you dont have a limit 1 on the end of the query. and aaron isnt that method really slow? (no backing on that statement)

tried it, although I have group by in there for something else.

Aaron · 07-30-2008, 08:33 PM

Then our SQL knowledge fails.

CoryMathews · 07-31-2008, 12:50 AM

Can you post the Query? or there isnt much else we can do.

Orc · 07-31-2008, 11:28 AM

sql Code:

SELECT
            t.tid,
            t.title AS thread_title,
            t.created,
            t.mid,
            t.fid,
            f.title AS forum_title,
            f.DESC  AS forum_desc,
            f.created AS forum_timestamp,
            fc.title AS fc_title,
            m.username AS member_username,
            m.mid AS member_uid,
            p.created AS post_time,
            p.msg AS post_msg,
            m.signature AS member_signature,
            p.pid,
            r.rid,
            COUNT(DISTINCT r.rid) AS rating_count
            FROM `threads` AS t
            
            LEFT JOIN `posts` AS p ON p.tid=t.tid
            LEFT JOIN `forums` AS f ON f.fid=t.fid
            LEFT JOIN `forum_categories` AS fc ON f.catid=fc.fcid
            LEFT JOIN `members` AS m ON m.mid=p.mid
            LEFT JOIN `member_ratings` AS r ON r.pid=p.pid
            
            
        
            
            
            WHERE t.tid = '".$tid."'
            AND   f.fid = '".$fid."'
            
            GROUP BY p.pid
            ORDER BY p.pid

I only get 1 row from member_ratings

CoryMathews · 07-31-2008, 01:59 PM

wow thats a hell of a query. Are you sure there even is more then 1 row that should be returned? you have quite a bit of restrictions on that. Also it might be better to break that into more then 1 query, I bet that runs hella slow im gonna guess at least 10 seconds, if the tables have some sort of data in them.

Orc · 07-31-2008, 02:03 PM

Tried multiple queries, didn't work, and I didn't like the idea in the first place.

By the way, it takes me 1 second. with a cache at least..

With cleared cache, it took 2 seconds. :P

Orc · 07-31-2008, 02:09 PM

Should I reconstruct my code to work with a multiple query? Cause that's what it is, or at least I believe it 100% :P

Orc · 07-31-2008, 02:38 PM

Nevermind, I DID reconstruct the queries and code, and I now have it where theres two queries for the post data and then for the ratings for the post data, inner while loops :p

Aaron · 07-31-2008, 03:05 PM

So is this solved?

Also, is it better to force the browser to cache your page, or cache it on the server, or both? O.o

Orc · 08-02-2008, 07:58 AM

Quote:

Originally Posted by Aaron

So is this solved?

Also, is it better to force the browser to cache your page, or cache it on the server, or both? O.o

cache on server I believe.