Hard sql query

Databases
1

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          percent
15                 2          percent
16.....            0          option

i want:

get 22 titles list

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

the queue order is:

first according to table 2(i want to use the count value = 1 minus the count value = 0. this is the content_id’s descending rule,

then table 1 (desc the nid create time) type is provider.

ps:For each content_id, the number of rows with value=1 minus the number of rows with value=0… each content_id has many value=0 or value=1

2

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

3

your explanation makes very little sense

this is as close as i got to understanding your very weird requirements –

SELECT 1 AS seq
     , content_id AS nid
     , COUNT(CASE WHEN value = 1
                  THEN 0 ELSE NULL END) -
       COUNT(CASE WHEN value = 0
                  THEN 1 ELSE NULL END) AS sort_value
  FROM votingapi_vote
UNION ALL
SELECT 2 AS seq
     , nid
     , created AS sort_value
  FROM node
 WHERE type = 'provider'
ORDER
    BY seq
     , id DESC
     , sort_value DESC LIMIT 22
4

table two structure {votingapi_vote}:
Code:


content_id      value    value_type

10                1          option
10                 0          option
11                 1          option
12                 0          option
15                 3          percent
15                 2          percent
16.....            0          option

output the content_id field and descending it, the rule is according to the value(the count of each content_id 's value=1 minus the count of each content_id 's value=0 ,each content_id has many value=0 or value=1). where value_type=option.

namely:For each content_id, the number of rows with value=1 minus the number of rows with value=0

is there a way to get that?

5 
SELECT content_id 
     , COUNT(CASE WHEN value = 1
                  THEN 0 ELSE NULL END) -
       COUNT(CASE WHEN value = 0
                  THEN 1 ELSE NULL END) AS diff
  FROM votingapi_vote
GROUP
    BY content_id

it appears i forgot the GROUP BY clause earlier,

6

i doesn’t understand this well. could you explain it . many thanks.

COUNT(CASE WHEN value = 1
THEN 0 ELSE NULL END) -
COUNT(CASE WHEN value = 0
THEN 1 ELSE NULL END) AS diff

7

the 1st COUNT function counts the number of 1 values

the 2nd COUNT function counts the number of 0 values

then the number of 0’s is subtracted from the number of 1’s, and the result is called “diff”