0

I have a survey record table which I want to count the latest survey recoders with field 'complete' is''Y', while the latest survay name is in another table with the createdate, that's mean, the table name is dynamic,

select 'complete' count(*)  
from( 
  select 'survey_name' 
  from survey 
  Where active=''Y'' 
  order by cratedate desc 
  limit 1
  )

but it cant work

record table: (name is dynamic)

id | complete | submitdate

survey table:

survay_name | active | cratedate

I knew it should be done by prepare statement, but my system (joomla + plotalot) only accept one query.

eagle
  • 17
  • 3

1 Answers1

0

You need to add 'id' column in your Survey table Of Course, the ID must link between SURVEY and RECORD table

then,, you can use below query

SELECT COUNT(r.complete) as complete, s.survay_name, s.createDate
FROM survey s
LEFT JOIN record r ON s.id = r.id
WHERE s.active = 'Y' AND r.complete = 'Y'
Kasnady
  • 2,249
  • 3
  • 25
  • 34
  • hi AKZhang, thanks for reply but the table is create by another system , So I can Not change the table structure. – eagle Oct 14 '16 at 06:02
  • Hi @eagle then you can't link these two table.. if you want to get COUNT, then you won't be able to get the survay_name.. or, did you have another table which link between the ID and survay_Name? – Kasnady Oct 14 '16 at 07:40
  • No, I think should use subquery or prepare statement. – eagle Oct 16 '16 at 09:40