union and join

[birbal]

i have three table forum,topic post. all are in relation with each other. column name status has some options pinned,normal.....i joined all the related tables and take the output and i limited the results per page by limit clause now the problem is it is showing up the last 10 msgs by last posted (its pined or general it is not caring)i want to show up the pined topic in top of every page and then the rest of ten messages.how can i do that by one query?i tried union the two tables (both are joined table). one for getting pinned one and one for the rest.making union into two joined tables will slow up the query???not sure it is the right technique or not. is there any better way?any guide will be apriciated.thank you!

[justsomeguy]

Post the query you have now with the join.

[birbal]

	 (				SELECT		a.tid,		a.subject,		a.owner AS towner,		a.born AS tborn,		a.status,		a.views,		b.owner AS powner,		b.born AS pborn,		count(b.pid) AS totalpost,		max(b.born) AS lastpost,		c.nick AS townername,		d.nick AS pownername		FROM topic a		LEFT OUTER JOIN post b		ON a.tid=b.topic		LEFT OUTER JOIN usergen c		ON a.owner=c.usrid		LEFT OUTER JOIN usergen d		ON b.owner=d.usrid		WHERE a.forum='%s' AND a.status='pined'				GROUP BY a.tid		ORDER BY lastpost DESC				)		UNION ALL		(				SELECT		a.tid,		a.subject,		a.owner AS towner,		a.born AS tborn,		a.status,		a.views,		b.owner AS powner,		b.born AS pborn,		count(b.pid) AS totalpost,		max(b.born) AS lastpost,		c.nick AS townername,		d.nick AS pownername		FROM topic a		LEFT OUTER JOIN post b		ON a.tid=b.topic		LEFT OUTER JOIN usergen c		ON a.owner=c.usrid		LEFT OUTER JOIN usergen d		ON b.owner=d.usrid		WHERE a.forum='%s' AND a.status!='pined'		GROUP BY a.tid		ORDER BY lastpost DESC		LIMIT %s,%s				 )

%s will replace by the data from sprintf()

[justsomeguy]

That seems like it should work, it should get all of the pinned topics and then the first of the non-pinned topics. Is that working?

[birbal]

That seems like it should work, it should get all of the pinned topics and then the first of the non-pinned topics. Is that working?

yes its working but only if there is atleast one pinned topic in a forum. otherwise it is showing a error...a.tid is NULL. and query is not executing after that.a.tid is primary key set to not null.is it the correct technique or i am doing it in wrong way and missing something?

[justsomeguy]

It seems strange if union raises an error when one of the sets is empty. Try running each query individually to see what they return, if they're empty or if they have rows with null values. Also, it will be more efficient to move your WHERE conditions into the join condition. e.g.:

		FROM topic a		LEFT OUTER JOIN post b		ON a.tid=b.topic AND a.forum='%s' AND a.status='pined'		...

[birbal]

It seems strange if union raises an error when one of the sets is empty. Try running each query individually to see what they return, if they're empty or if they have rows with null values. Also, it will be more efficient to move your WHERE conditions into the join condition. e.g.:

if their is nothing to show as pined topicfor the 1st set :it showing all NULL for the 2nd set: it is showing allfor both set:#1048 - Column 'tid' cannot be null

[birbal]

separately each joined table giving null fields if there are no any match. i thought it should return empty result.

[justsomeguy]

The left join means it will return all topics regardless of whether they have any matching rows in the other table, so you may want to replace that with an inner join.

[birbal]

The left join means it will return all topics regardless of whether they have any matching rows in the other table, so you may want to replace that with an inner join.

but in inner join it will not show the row(though topic is exist) if there is no post in a topic. is not it?

[justsomeguy]

Well yeah, but every topic has at least one post, right?

[birbal]

no..actually table topic holds the topic name and the first post as topic starter mesage. at the creation of the topic there will be no post to show.by the way. it is working now with outer join and giving empty set if there is no value to show in separate joined query.will it be better to change the db structure so after creating the topic the related mesasge will go to the post table instead of topic table?actually i avoided it cause i have to insert two times for creating a topic. and i am not sure how to insert two query at once for same event.but now i have to do some unnescearry extra coding for manage it for separating the "posts" (when i want to show the posts i have to touch the topic table too. and its weiring all) and it is creating more problems what i had thought before. its like some extra headech.confused what will be the better option.!.:/

[justsomeguy]

It makes sense to store all posts in the same table instead of distinguishing the first post and every other post.

[birbal]

ok i will change it then..thanks for the help!