CODEWITHSUNDEEP
phpmysqlsql
daker
daker

Reputation: 3540

Select data from multiple tables if content exists or else skip one table

I have a php-page displaying a gallery. Under all items I print some info like good/bad votes (thumbs up/down) and the number of views it has.

For every inserted row in content there will also be a row inserted into content_views table. However the votes table will only contain rows if anyone has voted (1vote = 1row).

I want to select all info (content.*, views and votes if there is any) within a certain category (niche). The problem I have is that my query (see below) will return an empty result if there is no votes.

    $result = mysql_query("
    SELECT content.*, content_views.views as views, votes.good, votes.bad 
    FROM content, content_niches, content_views , votes
    WHERE content_views.content = content.record_num 
    AND content.approved = 2 
    AND content_niches.content = content.record_num 
    AND votes.content = content.record_num
    AND content_niches.niche = '$niche' 
    AND content.enabled = 1 
    GROUP BY content.record_num 
    ORDER BY $orderby DESC LIMIT $from,$max_results") or die(mysql_error());

Ofcourse I can do the following but since I have >50K rows in content and even more in votes this will be very slow.

    $result = mysql_query("
    SELECT content.*, content_views.views as views,
            (SELECT votes.good FROM votes WHERE votes.content=content.record_num) AS good,
            (SELECT votes.bad FROM votes WHERE votes.content=content.record_num) AS bad
    FROM content, content_niches, content_views 
    WHERE content_views.content = content.record_num 
    AND content.approved = 2 
    AND content_niches.content = content.record_num 
    AND votes.video = content.record_num
    AND content_niches.niche = '$niche' 
    AND content.enabled = 1 
    GROUP BY content.record_num 
    ORDER BY $orderby DESC LIMIT $from,$max_results") or die(mysql_error());

What is the “correct” way to do this?

Edit1: New query that is way too slow, what to do?

    $result = mysql_query("
    SELECT content.*,content_niches.*, content_views.views as views 
    FROM content
    INNER JOIN 
       content_niches ON (content_niches.content = content.record_num AND content_niches.niche = '$chanid')
    INNER JOIN
       content_views ON (content_views.content = content.record_num )
    LEFT OUTER JOIN votes
       ON (votes.video = content.record_num)
    WHERE content.approved = 2 
    AND content.enabled = 1 
    GROUP BY content.record_num 
    ORDER BY $orderby DESC LIMIT $from,$max_results") or die(mysql_error());

Upvotes: 0

Views: 258

Answers (1)

Derek
Derek

Reputation: 23228

It's called an outer join. A left outer join returns all rows from the left side and only those that match from the right side.

So, for example, if you have a post with 1 vote and a post with 0 votes, and you do this:

select * 
from posts
left outer join votes
  on posts.id = votes.id

It would return all of the posts and any of the votes records that exist and correspond.

You should really learn to use ansi-join syntax instead of non-ansi joins

Upvotes: 1

Related Questions