Re: Trouble with Query
I think you want to try 'having mo.date IS NULL' after the order by. Daren wrote: I'm query for a list of offers from a table, but am trying to do a Left Join on the table that keeps track of which members have completed which offers (so that the query will not return offers that the member has already completed). Query: select distinct(ol.id) as id, mo.date from offers_listings ol left join member_offers mo on (mo.member_id = 1) and (ol.id = mo.offer_id) where (ol.location_id = 2) and (ol.active = 'Y') order by ol.weight desc limit 3; Results: +++ | id | date | +++ | 1 | 2004-06-04 | | 2 | NULL | | 3 | NULL | +++ So, member_id 1 has completed offer_id 1, but not offer_id's 2 or 3. I would assume that I simply need to add one more where clause to only return results with a non-null date. I tried: select distinct(ol.id) as id, mo.date from offers_listings ol left join member_offers mo on (mo.member_id = 1) and (ol.id = mo.offer_id) where (ol.location_id = 2) and (ol.active = 'Y') and (mo.date IS NULL) order by ol.weight desc limit 3; However, this does not work - I get an empty result. Which is *really* weird, because if I change the query to only return non-null values, like so: select distinct(ol.id) as id, mo.date from offers_listings ol left join member_offers mo on (mo.member_id = 1) and (ol.id = mo.offer_id) where (ol.location_id = 2) and (ol.active = 'Y') and (mo.date IS NOT NULL) order by ol.weight desc limit 3; It works perfectly: +++ | id | date | +++ | 1 | 2004-06-04 | +++ Of course, this is the opposite of what I want, so I'm quite confused. Can anyone point me in the right direction? TIA! __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble with Query
I'm query for a list of offers from a table, but am trying to do a Left Join on the table that keeps track of which members have completed which offers (so that the query will not return offers that the member has already completed). Query: select distinct(ol.id) as id, mo.date from offers_listings ol left join member_offers mo on (mo.member_id = 1) and (ol.id = mo.offer_id) where (ol.location_id = 2) and (ol.active = 'Y') order by ol.weight desc limit 3; Results: +++ | id | date | +++ | 1 | 2004-06-04 | | 2 | NULL | | 3 | NULL | +++ So, member_id 1 has completed offer_id 1, but not offer_id's 2 or 3. I would assume that I simply need to add one more where clause to only return results with a non-null date. I tried: select distinct(ol.id) as id, mo.date from offers_listings ol left join member_offers mo on (mo.member_id = 1) and (ol.id = mo.offer_id) where (ol.location_id = 2) and (ol.active = 'Y') and (mo.date IS NULL) order by ol.weight desc limit 3; However, this does not work - I get an empty result. Which is *really* weird, because if I change the query to only return non-null values, like so: select distinct(ol.id) as id, mo.date from offers_listings ol left join member_offers mo on (mo.member_id = 1) and (ol.id = mo.offer_id) where (ol.location_id = 2) and (ol.active = 'Y') and (mo.date IS NOT NULL) order by ol.weight desc limit 3; It works perfectly: +++ | id | date | +++ | 1 | 2004-06-04 | +++ Of course, this is the opposite of what I want, so I'm quite confused. Can anyone point me in the right direction? TIA! __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Rookie having trouble with query containing several outer joins
* [EMAIL PROTECTED] > I'm new to MySQL and have tried many attempts myself and looked > all over for > this answer to no avail. How do I write MySQL joins to accomodate the > Oracle equivalent listed belowany help would be appreciated. > > From > iteration, > story, > person tracker, > person customer, > person developer, > task, > time_entry > Where > iteration.id=story.iteration_id and > story.tracker_id=tracker.id(+) and > story.id=task.story_id(+) and > story.customer_id=customer.id(+) and > task.acceptor_id=developer.id(+) and > task.id=time_entry.task_id(+) > > I've got this so far, but it seems to be returning a cartesian product > between iteration and story > [...] Ouch. I find this syntax easier: From iteration left join story on iteration.id=story.iteration_id left join person tracker on story.tracker_id=tracker.id left join person customer on story.customer_id=customer.id left join task on story.id=task.story_id left join person developer on task.acceptor_id=developer.id left time_entry on task.id=time_entry.task_id The WHERE clause is eliminated in this case. Had to move task before developer, the tables are read in the order you provide when using left joins. http://www.mysql.com/doc/en/JOIN.html > -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Rookie having trouble with query containing several outer j oins
I'm new to MySQL and have tried many attempts myself and looked all over for this answer to no avail. How do I write MySQL joins to accomodate the Oracle equivalent listed belowany help would be appreciated. From iteration, story, person tracker, person customer, person developer, task, time_entry Where iteration.id=story.iteration_id and story.tracker_id=tracker.id(+) and story.id=task.story_id(+) and story.customer_id=customer.id(+) and task.acceptor_id=developer.id(+) and task.id=time_entry.task_id(+) I've got this so far, but it seems to be returning a cartesian product between iteration and story SELECT task.name, developer.name, time_entry.start_time, story.name, customer.name, tracker.name, iteration.name FROM ( ( (story left outer join (task left outer join time_entry on task.id=time_entry.task_id left outer join person as developer on task.acceptor_id=developer.id ) on story.id=task.story_id ) left outer join person as customer on story.customer_id=customer.id ) left outer join person as tracker on story.tracker_id=tracker.id ) join iteration on story.iteration_id=iteration.id
Trouble with Query using DWMX
Have a problem with this query, when xBatch is empty or at least seems to be empty. Using DWMX, MySQL, and PHP SELECT donations.id, donations.batch, donations.oikos_id, donations.giftDate, donations.genFund, donations.misFund, donations.benFund, donations.notes, oikos.id, oikos.famLabel FROM donations, oikos WHERE IF(xBatch = '', donations.batch > 0, donations.batch = xBatch) AND donations.oikos_id = oikos.id ORDER BY donations.batch, donations.giftDate, donations.oikos_id xBatch = $HTTP_GET_VARS['searchBatch'] - Comes from a form field named "searchBatch." When "searchBatch" is empty and thus makes xBatch empty, I get the following SQL error. You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '= '', donations.batch > 0, donations.batch = ) AND donations.oi Why isn't the conditional statement fixing the problem? Any suggestions? -Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
