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" <m...@davemaharaj.com> 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+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en