left join or right join in a MySQL query?

table1: node
fields: nid, ,title,type,created

query1:

mysql_query(SELECT nid,title, type,created FROM `node` where type="provider"  ORDER BY created DESC LIMIT 0 , 22)



table2:  votingapi_vote
fields:   content_id, value=1 or  value=0, value_type=option

query2:

SELECT content_id,
       SUM(CASE WHEN value=1 THEN 1 
                WHEN value=0 THEN -1 
                ELSE 0 
           END) AS ContentSum
    FROM votingapi_vote
    WHERE value_type = 'option'
    GROUP BY content_id
    ORDER BY ContentSum DESC

content_id value equals nid value, but in table 1, the nid maybe not has the one - to - one correspondence to the table 2. eg:

table 1     table2
nid          content_id

1              1
2              3
3

but the content_id has one - to - one correspondence to the nid in table 1.

now, i want to get a title list. which unmber is 22. the descending order is according to ContentSum and created. is there a way to get this? should i use left join? i don't know how to make the two query turn into one?

a hard query to write in mysql to me?

rephrase it:

table one structure {node}:

nid        type             title                 created

10          provider        test one              1298107010
11          provider        test two              1298107555    
12          provider        example one            1300524695 
13          provider        example two            1298081391
14          provider        example three          1298082340
15          company         example four           1298083519
16....      company         example five          1298083559

table two structure {votingapi_vote}:

content_id      value    value_type

10                1          option
10                 0          option
11                 1          option
12                 0          option
15                 3    开发者_如何学C      percent
15                 2          percent
16.....            0          option

i want:

get 22 titles list

...
test one
test two
example one
example two 
...

1, the value of nid is equals the value of content_id in table 2.

the title list queue order is:

1, first according to table 2 content_id descending the tile list(decending content_id using "For each content_id, the number of rows with value=1 minus the number of rows with value=0" )

2, because table2 maybe less than 22 records and has the same value when the number of rows with value=1 minus the number of rows with value=0. when emerge this condition. using the created field in table 1 to descending the tile

<table1> left join <table2>

Means that tuples do not have to have a matching element in table2 but all elements in table1 will be included.

<table1> right join <table2>

Means that tuples do not have to have a matching element in table1 but all elements in table2 will be included.

With the example you've provided some of it is not clear to me. I'll just give a query that combining the two query you've provided.

Try this

SELECT nid,title, type,created, v_api.ContentSum
  FROM `node` n
  LEFT JOIN ( SELECT content_id,
                     SUM(CASE WHEN value=1 THEN 1 
                         WHEN value=0 THEN -1 
                         ELSE 0 
                     END) AS ContentSum
                FROM votingapi_vote
               WHERE value_type = 'option'
               GROUP BY content_id) v_api 
    ON n.nid = v_api.content_id
 where type="provider"
ORDER BY v_api.ContentSum DESC, created DESC LIMIT 0 , 22;

Note: you can remove some of the columns that you don't need.

Other things to know:

• Using LEFT JOIN will also create a row that doesn't have an equivalent nid to content_id. That's the reason I added v_api.ContentSum for you to see that it will create a null value.
• You can use COALESCE if you want to add a value if it's return null. You can use INNER JOIN If you don't want to include the row that have a v_api.ContentSum equal to null.
• About RIGHT JOIN I'm not sure if this is what you need with regards to your question base on my understanding.

Also take a look on this mysql/join.

I hope this can be a help.