RE: Help with Joins
Thanks...i will give that a go. Y our right in your reply with the sub_query after looking at what I was trying. Dave -Original Message- From: Mattijs [mailto:mattijsmeib...@hotmail.com] Sent: November-13-10 6:37 AM To: CakePHP Subject: Re: Help with Joins You are actually joining records in your example, while you want the records without a join. You could use a subquery with NOT IN instead: SELECT * FROM Offers WHERE Offer.id NOT IN (SELECT Reject.offer_id FROM Rejects WHERE Reject.profile_id = '4b4ff09c-2580-4e21-9dbf-36b74adcd75b') Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
Re: Help with Joins
you want to show all offers that the user not rejected? Your SQL statement returns only all offers that the user rejected. Thats not what you want? please correct me, when i do misunderstand you. Your SQL statement returns a table with rows of offers combinded with rejects where offer id = reject offer id and the reject user id = customer id but you want rows of rejects combinded with offers where reject offer id = offer id and reject id IS NULL and reject user id = customer id. When you want to realize this you have to alter the order of tables for the left join or you use a right join. SELECT DISTINCT `Offer`.`id` , `Offer`.`title` , `Reject` . * FROM `Offers` AS `Offer` RIGHT JOIN `Rejects` AS `Reject` ON ( `Offer`.`id` = `Reject`.`offer_id` ) WHERE `Reject`.`offer_id` IS NULL AND `Reject`.`profile_id` = '4b4ff09c-2580-4e21-9dbf-36b74adcd75b' ORDER BY `Offer`.`created` DESC LIMIT 15 please try this for all unrejected offers from profile_id = '4b4ff09c-2580-4e21-9dbf-36b74adcd75b'. greetings On 12 Nov., 23:17, "Dave Maharaj" wrote: > No matter what I try I cant get this to work. > > I need to pull all records from Offer only if not in Reject > > I tried manual JOINS but all I ever get is a list of all Reject records. > > SELECT DISTINCT `Offer`.`id` , `Offer`.`title` , `Reject` . * > > FROM `Offers` AS `Offer` > > LEFT JOIN `Rejects` AS `Reject` ON ( `Offer`.`id` = `Reject`.`offer_id` ) > > WHERE `Reject`.`offer_id` = `Offer`.`id` > > AND `Reject`.`profile_id` = '4b4ff09c-2580-4e21-9dbf-36b74adcd75b' > > ORDER BY `Offer`.`created` DESC > > LIMIT 15 > > Just trying the query directly in SQL phpMyAdmin window, then if I can get > it to work I will put it in the model.. > > Basic English is there are 100 Offers as User goes thru offers they Reject > which creates a record in Reject with id, profile_id and offer_id > > So the offer list that has 100 total records / user rejected 25 so that's > users list will actually only display 75 records. > > Offers are for various users so simply deleting the offer is not an option > > Tried=> AND `Reject`.`Offer_id` != `Offer`.`id`but nothing is returned > > Ideas? Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
Re: Help with Joins
You are actually joining records in your example, while you want the records without a join. You could use a subquery with NOT IN instead: SELECT * FROM Offers WHERE Offer.id NOT IN (SELECT Reject.offer_id FROM Rejects WHERE Reject.profile_id = '4b4ff09c-2580-4e21-9dbf-36b74adcd75b') On 12 nov, 23:17, "Dave Maharaj" wrote: > No matter what I try I cant get this to work. > > I need to pull all records from Offer only if not in Reject > > I tried manual JOINS but all I ever get is a list of all Reject records. > > SELECT DISTINCT `Offer`.`id` , `Offer`.`title` , `Reject` . * > > FROM `Offers` AS `Offer` > > LEFT JOIN `Rejects` AS `Reject` ON ( `Offer`.`id` = `Reject`.`offer_id` ) > > WHERE `Reject`.`offer_id` = `Offer`.`id` > > AND `Reject`.`profile_id` = '4b4ff09c-2580-4e21-9dbf-36b74adcd75b' > > ORDER BY `Offer`.`created` DESC > > LIMIT 15 > > Just trying the query directly in SQL phpMyAdmin window, then if I can get > it to work I will put it in the model.. > > Basic English is there are 100 Offers as User goes thru offers they Reject > which creates a record in Reject with id, profile_id and offer_id > > So the offer list that has 100 total records / user rejected 25 so that's > users list will actually only display 75 records. > > Offers are for various users so simply deleting the offer is not an option > > Tried=> AND `Reject`.`Offer_id` != `Offer`.`id`but nothing is returned > > Ideas? Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
Help with Joins
No matter what I try I cant get this to work. I need to pull all records from Offer only if not in Reject I tried manual JOINS but all I ever get is a list of all Reject records. SELECT DISTINCT `Offer`.`id` , `Offer`.`title` , `Reject` . * FROM `Offers` AS `Offer` LEFT JOIN `Rejects` AS `Reject` ON ( `Offer`.`id` = `Reject`.`offer_id` ) WHERE `Reject`.`offer_id` = `Offer`.`id` AND `Reject`.`profile_id` = '4b4ff09c-2580-4e21-9dbf-36b74adcd75b' ORDER BY `Offer`.`created` DESC LIMIT 15 Just trying the query directly in SQL phpMyAdmin window, then if I can get it to work I will put it in the model.. Basic English is there are 100 Offers as User goes thru offers they Reject which creates a record in Reject with id, profile_id and offer_id So the offer list that has 100 total records / user rejected 25 so that's users list will actually only display 75 records. Offers are for various users so simply deleting the offer is not an option Tried=> AND `Reject`.`Offer_id` != `Offer`.`id`but nothing is returned Ideas? Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
