4 replies to this topic

[Eric The Red]

I've got this big fat call:

SELECT p.id, p.prop_name, p.bedroom_num, p.bathroom_num, p.price_on, p.price_off, s.setting, l.location, p.instruct, p.sleeps, p.sq_ft, p.features, p.tnum, p.park_num, p.park_secure, p.heated, p.acres, p.pets, p.pets_comments, p.child, p.child_comments, p.bed1_floor, p.bed2_floor, p.bed3_floor, p.bed4_floor, p.bed5_floor, p.bed6_floor, p.bed7_floor, p.bed8_floor, p.bed9_floor, p.bed10_floor, p.bed1_type, p.bed2_type, p.bed3_type, p.bed4_type, p.bed5_type, p.bed6_type, p.bed7_type, p.bed8_type, p.bed9_type, p.bed10_type, p.add_bed, p.bathroom_num, p.bath_num, p.shower_num, p.toilet_num, p.bath1_floor, p.bath2_floor, p.bath3_floor, p.bath4_floor, p.bath5_floor, p.bath6_floor, p.bath7_floor, p.bath8_floor, p.bath9_floor, p.bath10_floor, p.bath1_type, p.bath2_type, p.bath3_type, bath4_type, p.bath5_type, p.bath6_type, p.bath7_type, p.bath8_type, p.bath9_type, p.bath10_type, lt.prop_type, p.listing_type, p.book_active, p.caretaker_suite, p.wheelchair, p.olympics, p.verified, p.you_tube, ph.photo1 
FROM prop_info p 
LEFT JOIN location_code l ON p.location=l.id
LEFT JOIN listing_type lt ON p.prop_type=lt.id
LEFT JOIN setting_code s ON p.setting=s.id
LEFT JOIN rates r ON p.id=r.prop_num    
LEFT JOIN photo ph ON p.id=ph.prop_num        
WHERE p.id=:id

and I have one more thing to add...

SELECT id FROM testimonials WHERE prop_num=:id AND post_agreed=1 AND active=1

but I am not sure how to do that. Can I just add WHERE p.id=:id and testimonials .post_agreed=1 and testimonials .active=1 ? And what if there are multiple records that will match that particular call? Is that a problem? Would that not change the way the original call is being found since it will only match if the other two values are 1?

As usual, thanks for the feedback!

[WingedPanther]

You may need something like:

SELECT p.id, p.prop_name, p.bedroom_num, p.bathroom_num, p.price_on, p.price_off, s.setting, l.location, p.instruct, p.sleeps, p.sq_ft, p.features, p.tnum, p.park_num, p.park_secure, p.heated, p.acres, p.pets, p.pets_comments, p.child, p.child_comments, p.bed1_floor, p.bed2_floor, p.bed3_floor, p.bed4_floor, p.bed5_floor, p.bed6_floor, p.bed7_floor, p.bed8_floor, p.bed9_floor, p.bed10_floor, p.bed1_type, p.bed2_type, p.bed3_type, p.bed4_type, p.bed5_type, p.bed6_type, p.bed7_type, p.bed8_type, p.bed9_type, p.bed10_type, p.add_bed, p.bathroom_num, p.bath_num, p.shower_num, p.toilet_num, p.bath1_floor, p.bath2_floor, p.bath3_floor, p.bath4_floor, p.bath5_floor, p.bath6_floor, p.bath7_floor, p.bath8_floor, p.bath9_floor, p.bath10_floor, p.bath1_type, p.bath2_type, p.bath3_type, bath4_type, p.bath5_type, p.bath6_type, p.bath7_type, p.bath8_type, p.bath9_type, p.bath10_type, lt.prop_type, p.listing_type, p.book_active, p.caretaker_suite, p.wheelchair, p.olympics, p.verified, p.you_tube, ph.photo1 
FROM prop_info p 
LEFT JOIN location_code l ON p.location=l.id
LEFT JOIN listing_type lt ON p.prop_type=lt.id
LEFT JOIN setting_code s ON p.setting=s.id
LEFT JOIN rates r ON p.id=r.prop_num    
LEFT JOIN photo ph ON p.id=ph.prop_num        
WHERE p.id in (SELECT id FROM testimonials WHERE prop_num=:id AND post_agreed=1 AND active=1)

[Eric The Red]

I see your point but I am not sure that would return the results I am after.

Basically what I am trying to do is to return the testimonials (of which there may be multiple) from that table where the prop_num is xx and the testimonials are actually active and agreed to post.

I do not want to limit the returns from the prop_info table to only those that have active testimonials though. Not all records will have related testimonials...

I will continue to work towards a solution as still do not know how to join the testimonials table where prop_num is the same as the record pulled from the main table BUT also where
post_agreed=1 AND active=1

Thanks again

[WingedPanther]

perhaps if you posted the DDL for your tables it would help.

[Eric The Red]

Sorry for the delay in getting back but work got in the way :-)

I ended up doing this a different way since I couldn't figure out how to get it to work.

What I was trying to do, but simplified, was:

Pull a bunch of records using WHERE pid=pid
And all the tables being joined to that are also looking for pid=pid
except for one which is looking for records where pid=pid but ALSO active=1 in that table alone.