output rows from multiple table

[runrunforest]

Hi,

I have a mysql result which contains a number of rows fetched from 3 tables.

So normally, if I want to count the number of row from mysql result and if its one table I would do this mysql_num_rows($result);

Since I want to know the number of rows fetched from one of the tables, I can not do the code above as it will give the combined result of 3 tables.

How would you do in this situation ?

[UFTimmy]

Hi,

I have a mysql result which contains a number of rows fetched from 3 tables.

So normally, if I want to count the number of row from mysql result and if its one table I would do this mysql_num_rows($result);

Since I want to know the number of rows fetched from one of the tables, I can not do the code above as it will give the combined result of 3 tables.

How would you do in this situation ?

Can you show us your SQL?

[runrunforest]

Yes here.

"SELECT a.* 
		, b.thumb_id
		, c.amount
	FROM listing a
	LEFT OUTER 
	JOIN listing_photo b
	ON ( a.id = b.listing_id)
	LEFT OUTER
	JOIN listing_bid c
	ON (a.id = c.listing_id)
	WHERE a.activation = 1
	AND b.key = 1"

I want to count how many amount selected.

[r937]

SELECT a.* 
     , b.thumb_id
     , ( SELECT COUNT(*)
           FROM listing_bid 
          WHERE listing_id = a.id ) AS c_rows
     , c.amount
  FROM listing a
LEFT OUTER 
  JOIN listing_photo b
    ON b.listing_id = a.id
   AND b.key = 1
LEFT OUTER
  JOIN listing_bid c
    ON c.listing_id = a.id
 WHERE a.activation = 1

[runrunforest]

Just looking at some random query you know exactly what to do with it. Genius.

[runrunforest]

Hi,

I have a query involved 3 tables, the number of rows pulled from 3 tables are different

For instance, a gives me 1 row, b gives me 1 row, c gives me 10 rows

How could I can output all c.amount ?

"SELECT a.* 
			, b.thumb_id
			, c.amount
			, ( SELECT COUNT(*)
        		FROM listing_bid 
      		     WHERE listing_id = a.id ) AS c_rows	
		FROM listing a
		LEFT OUTER 
		JOIN listing_photo b
		  ON b.listing_id = a.id
		 AND b.key = 1
		LEFT OUTER
		JOIN listing_bid c
		  ON c.listing_id = a.id
	   WHERE a.activation = 1";