SQL

From Code Trash

Three table join and count

I have table blog, blog_category and blog_comments and i want to display list of posts form blog table with the category name form category table and count of comments for a post form the comments table

Table Structure

Blog - title, content

Blog_category - Name

Blog_comment - postid, comment

i want to display posts, no of approved comments, no of unapproved comments, categgory name and some more. selecting from three tables.

and the query i used was

select a.*, b.name, (select count(*) as okcount from blog_comments where a.id=blog_comments.bid and blog_comments.astatus=1) as okcount ,(select count(*) as nookcount from blog_comments where a.id=blog_comments.bid and blog_comments.astatus=0) as nookcount from blog a, blog_category b, blog_comments c where a.cid=b.id group by a.id order by a.id

The above code worked if and only if all three tables had values... that is if and only if there is an entry in blog_comments.

so an alternate has been used in which if there are no rows then query one is executed and if there is then query 2 is executed. The following is query 2.

SELECT 0 AS hoho, blog.*, blog_category.name AS catname, blog_comments.name FROM blog 
LEFT JOIN blog_category ON (blog.cid=blog_category.id) 
LEFT JOIN  blog_comments ON blog.id = blog_comments.bid

Alphabet range query using regular expression

	$range = $_REQUEST[char_range];
	if($range=='A-F')
	{
		$filter = "email REGEXP '^[A-F]'";
	}elseif($range=='G-J'){
		$filter = "email REGEXP '^[G-J]'";
	}elseif($range=='K-N'){
		$filter = "email REGEXP '^[K-N]'";
	}elseif($range=='O-R'){
		$filter = "email REGEXP '^[O-R]'";	
	}elseif($range=='S-W'){
		$filter = "email REGEXP '^[S-W]'";	
	}elseif($range=='X-Z'){
		$filter = "email REGEXP '^[X-Z]'";	
	}
	/*
	else{
		$filter = "mem_fname REGEXP '^[A-Z]'";	
	}
	*/
 
  $limit=10;
  if($filter){
	  $SQL="SELECT * from k_nonsubscriber where $filter order by email asc";
  }else{
	 // $filter = "email REGEXP '^[A-Z]'";	
	  $SQL = "SELECT * from  k_nonsubscriber order by email asc";
  }
   $npage=$object->pagnation($SQL,$limit);
 
  if(isset($_REQUEST[p]))	$sli=($_REQUEST[p]-1)*$limit; else $sli=0;
  $SQL.=" LIMIT $sli , $limit";
 
  $buy = mysql_query("$SQL") or die(mysql_error());
  $count = mysql_num_rows($buy);

How to INNER JOIN a COUNT with a SELECT statement

Actually, a subselect may be better then:

SELECT p.id, p.worktitle, p.enddate, p.views, (
      SELECT COUNT(*) AS allBids
      FROM bids
      WHERE bids.pid = p.pid
      GROUP BY pid
      ) AS allBids
FROM postings p
WHERE p.createdby='4'
ORDER BY p.enddate ASC

Inner join for distinct left table

I have two tables and i want the rows from the left table iff there are any reference to that in the right table. But without duplication. Here is a code. I hope if there is a row from the right which does not have reference in left will also be listed. i have not checked. This table is structured to have rows in the right table iff there are reference for it in the left table.

SELECT a.* FROM lefttable a 
     INNER JOIN righttable b
     ON a.id=b.gid 
     GROUP BY a.id 
     ORDER BY a.position

Order by null values first

Order mysql result so that null values comes first

ORDER BY IFNULL(colname,0),colname

Order by null values last

Order mysql result so that null values comes last ==

WHERE case
        when somecolumn IS NULL then 0
        else 1
      end ASC
    , somecolumn ASC

why case ?

ORDER BY spalte IS NULL

sry, better use the function instead XD

ORDER BY ISNULL(spalte)

Reference

• http://www.bigresource.com/Tracker/Track-mysql-XjvprXdN/

Order by the values in IN() clause

SELECT * FROM your_table
WHERE id IN (5,2,6,8,12,1)
ORDER BY FIELD(id,5,2,6,8,12,1);

Reference

• http://stackoverflow.com/questions/2185029/sort-by-order-of-values-in-a-select-statement-in-clause-in-mysql

References

• http://www.bigresource.com/PHP-Table-Join-Count-Records--4dzr9bk1.html - Table join count records - sometimes later i need to check the link for various join and count resources
• LEFT JOIN and COUNT help - dev shed forum
• sql select into statement