How to do join

[wally1234]

I have 2 tables and want to add the count column from the bottom table to the top table (join on “OID” and “OPTID”) and if no count is found set the cell to zero. How can I do this?

SELECT  questions.pollid, questions.id, questions.title, 
questions.ordering, options.oid,
options.qoption, options.ordering FROM questions, options WHERE 
(questions.pollid =  2 AND 
questions.type = 1 AND
questions.ordering = 1) AND
(questions.id = options.quid)

pollid id       title                        ordering  Oid      qoption    ordering 
2	4	Overall customer experience	1	10	Excellent	1
2	4	Overall customer experience	1	11	Very good	2
2	4	Overall customer experience	1	12	Average	        3
2	4	Overall customer experience	1	13	Poor	        4
2	4	Overall customer experience	1	14	Terrible	5

SELECT count(*), optid FROM poll_data WHERE block = 0 GROUP BY optid;

count( * ) optid 
2	4
2	11
1	12
1	13
1	14
2	16
1	18
1	19

[jlego]

SELECT COUNT(questions.id) as q_count, COUNT(option.id) as o_count FROM QUESTIONS INNER JOIN OPTION

i dont know what you are joining them on

is this what you are trying to do?

[wally1234]

I am trying to join on 0id (top table) and optid (bottom table) to add count from the bottom table to the top table. However when I do this it drops row 0id 10 as there is no count for this row in the bottom table. I want row 0id 10 set to zero if there is no row found.

[jlego]

IF(ISNULL(Oid), 0, Oid) as Oid

?

[wally1234]

IF(ISNULL(Oid), 0, Oid) as Oid

?

Thanks for your reply but where do I add this piece of code?

[jlego]

SELECT  questions.pollid, questions.id, questions.title, questions.ordering,


 IF(ISNULL(options.oid), 0, option.oid) as Oid,

options.qoption, options.ordering FROM questions, options WHERE (questions.pollid =  2 AND questions.type = 1 ANDquestions.ordering = 1) AND(questions.id = options.quid)SELECT  questions.pollid, questions.id, questions.title, 
questions.ordering, options.oid,
options.qoption, options.ordering FROM questions, options WHERE 
(questions.pollid =  2 AND 
questions.type = 1 AND
questions.ordering = 1) AND
(questions.id = options.quid)

[wally1234]

I tried your solution but it doesn't work. I've subsequently replaced the 2nd select with

SELECT count(*), poll_data.optid FROM poll_data WHERE poll_data.block = 0 GROUP BY poll_data .optid;

as follows:

SELECT questions.pollid, questions.id, questions.title, questions.ordering,

 IF(ISNULL(options.oid), 0, options.oid) as Oid,

options.qoption, options.ordering FROM questions, options 
WHERE (questions.pollid =  2 AND questions.type = 1 AND questions.ordering = 1) AND
(questions.id = options.quid)
SELECT questions.pollid, questions.id, questions.title, questions.ordering,
options.oid, options.qoption, options.ordering 
FROM questions, options 
WHERE (questions.pollid = 2 AND questions.type = 1 AND questions.ordering = 1) AND
(questions.id = options.quid)

SELECT count(*), poll_data.optid FROM poll_data WHERE poll_data.block = 0 GROUP BY poll_data .optid;

but this doesn't work either. Any ideas?

[jlego]

i dont know how well i can explain

..

does any of the tables you are pulling from contain a row 10?

if they dont, then it wont show a row 10

the only way it will show a row ten if is say table 1 had a row 10, but table 2 didnt. so table 2 would spit a null, and the if could catch it and make it a 0

but i think both tables are stopping at row 9 in your case

there might be another way to do this, i just dont know of it?

[wally1234]

In my example above the SQL statements create the contents in the 2 tables. Table 1 contains row 1 with the value 10 under Oid but in table 2 under OPTID there is no value 10. When I try to join the 2 tables on 0id and OPTID using the SQL above then row 1 in table 1 is dropped as there is no match in table 2. Basically I want table 1 to be appended with the value (count column) from table 2 if that makes sense

[jlego]

O.

Use a left join

IF(ISNULL(COLUMN), 0, COLUMN) as OID FROM table1 LEFT JOIN table2

[wally1234]

Managed to resolve the problem as follows:

SELECT  count(optid) AS number , questions.pollid, questions.title, q.id, q.quid, q.qoption, q.ordering, o.id, o.optid
FROM questions, options AS q
LEFT JOIN data AS o ON o.optid = q.id
WHERE (
questions.pollid =2
AND questions.type =1
AND questions.ordering =1
)
AND (
questions.id = q.quid
)
GROUP BY optid

[jlego]

it returned a null?