left join on same table in mysql -

-

i trying apply left join on same table not getting desired results.

i have table

table -- pages

id    name        parent        status 1     aaa         0             draft 2     bbb         1             live 3     ccc         13            live 4     ddd         0             live 5     eee         4             live 6     fff         4             live

i want rows id status live , parent id's status live.so in above example id 2 should not appear it's parent id 1 status draft.

i have made sqlfiddle -- http://www.sqlfiddle.com/#!2/d6b31/4

any highly welcomed. in advance.

make sure fiddle's schema includes cases. cases provided did not include child not live without parent.

this should expecting.

select a.id, a.name, a.parent, a.status pages left join pages b on a.parent = b.id , b.status='live' a.status='live' , (b.status='live' or a.parent=0)

adjusted cases:

(1, 'aaa', '0', 'draft'), (2, 'bbb', '1', 'live'), (3, 'ccc', '13', 'live'), (4, 'ddd', '0', 'live'), (5, 'eee', '4', 'live'), (6, 'fff', '4', 'draft'), (7, 'ggg', '0', 'draft')

Comments

Post a Comment

Popular posts from this blog

Prolog - Listing -

-
i have problem trying work out code here. let's have these codes: university('university of cambridge', 1, 'united kingdom', 90.3, 92.8, 89.4), university('university of oxford', 2, 'united kingdom', 88.9, 94.8, 88.0). how list out names of universities only? tried doing listing. , listing(university). list out or rules. trying names like: university of cambridge university of oxford this gives 1 name: ?: university(x,_,_,_,_). you can query more ; or space to print of them, use fail driven loop: ?: university(x,_,_,_,_), write(x), nl, fail; true.
Read more

orchardcms - change base url in local copy in Orchard CMS -

-
i want copy website (orchard 1.7.2.0) localhost, test things before on live site. ik copied database , de web directory , configured iis. in orchards database changed base url in settings_sitesettings2partrecord table. changed database connection in settings.txt now when enter new base url in browser redirected original url. how can prevent this? can't find other reference original url. thanks in advance answer. i found out went wrong. there rewrite in database originial url (for removing www in url). removed , works.
Read more

linear regression - Trying to get confidence/prediction intervals with `predict.lm` in R, but I keep getting an error regarding my dichotomous variable -

-
i have dataset looks this: time size type 1 22 151 0 2 31 92 0 3 26 175 0 4 35 31 0 5 27 104 0 6 5 277 0 7 17 210 0 8 24 120 0 9 9 290 0 10 21 238 0 11 33 164 1 12 20 272 1 13 16 295 1 14 43 68 1 15 36 85 1 16 26 224 1 17 25 166 1 18 18 305 1 19 35 124 1 20 19 246 1 my aim simple: run regression , confidence/prediction intervals. i run regression so: fit.lm1<-lm(time~size+type,data=project3) i want 95% confidence intervals , 95% prediction intervals mean time when size equal 200. want cis/pis type = 0 , type = 1. code is: new_val <- data.frame(size= c(200,200),type=c(1,0)) ci<-predict(fit.lm1,newdata=new_val,interval="confidence") pi<-predict(fit.lm1,newdata=new_val,interval="prediction") i following errors: error: variable 'type' fitted type "factor" type "numeric"...
Read more