CODEWITHSUNDEEP
mysql
Sean Kimball
Sean Kimball

Reputation: 4494

MySQL Query, how to select data based on a condition in a different record in the same table?

Having some trouble getting my here around this query & wondering if anyone can help:

I'm trying to select all the tvv.value[s] from the modx_site_tmplvar_contentvalues where the tvv.tmplvarid = 1 and the tvv.value of tvv.tmplvarid = 3 is a date greater then or equal to today.

So basically each modx_site_content.id that is published an has a parent of '24' will have at least 2 entries in the modx_site_tmplvar_contentvalues table one with an tmplvarid of '1' whose value will be a comma separated list and another with a tmplvarid of '3' which will be a datestamp.

I need to select all the values from the modx_site_tmplvar_contentvalues table where the tmplvarid is '1' [the comma separated list], where the second entry tmplvarid equaling '3' is a date greater than equal to today and the modx_site_content conditions are published = '1' and parent = '24'

Here is my query [that does not work]

SELECT sc.id, sc.pagetitle, tvv.value, tvv.tmplvarid, tvv.id
FROM modx_site_content sc
left join modx_site_tmplvar_contentvalues tvv on tvv.contentid = sc.id
where published = '1' 
and parent = '24'
and (tvv.tmplvarid = '3' or tvv.tmplvarid = '1')
and tvv.value >= curdate()
group by sc.id
order by sc.id

Any help?

Upvotes: 0

Views: 914

Answers (1)

Thinhbk
Thinhbk

Reputation: 2214

If my understanding is correct, you want to get data based on 2 conditions:

 - tmplvarid = '1'
 - tmplvarid = '3' and value >= curdate()

If so, try this:

SELECT sc.id, sc.pagetitle, tvv.value, tvv.tmplvarid, tvv.id
FROM modx_site_content sc
left join modx_site_tmplvar_contentvalues tvv on tvv.contentid = sc.id
where published = '1' 
and parent = '24'
and ((tvv.tmplvarid = '3' and tvv.value >= curdate()) or (tvv.tmplvarid = '1'))
order by sc.id

Correct me if I misunderstand your question.

Upvotes: 1

Related Questions