MySql query help

  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

I have the following tables: `data`, `data_link`, `user`, `poll` and `poll_answer`. The tables `user`, `poll` and `poll_answer` each have an `id` field which they get from the `data` table. The `data_link` table just links two `id`'s together, for instance a `poll` and it's `poll_answer`(s).

so the data would look something like follows:

user (3) [3 is the `id`].
poll (12)
poll_answer (13)
poll_answer (14)

in the `data_link` table `id`s 13 and 14 will both be linked to 12, for those are the two possible `poll_answer`s to `poll` question 12.

When a `user` answers/votes on a `poll` question, I link the `user` (3) to the `poll_answer` (14) through the `data_link` table.

My query to get `poll`s that a `user` hasn't voted on doesn't work properly though, here is what I have so far:

MYSQL Code: [ Select ]
SELECT DISTINCT(`poll`.`id`)
FROM `poll`
 
JOIN `data` ON `poll`.`id` = `data`.`id`
JOIN `data_link` ON `poll`.`id` = `data_link`.`link`
JOIN `poll_answer` ON `data_link`.`id` = `poll_answer`.`id`
LEFT JOIN `data_link` AS `data_link_2` ON `poll_answer`.`id` = `data_link_2`.`link`
LEFT JOIN `user` ON `data_link_2`.`id` = `user`.`id`
 
WHERE `data`.`status` != 'D'
AND (`user`.`id` != 3 OR `user`.`id` IS NULL);
  1. SELECT DISTINCT(`poll`.`id`)
  2. FROM `poll`
  3.  
  4. JOIN `data` ON `poll`.`id` = `data`.`id`
  5. JOIN `data_link` ON `poll`.`id` = `data_link`.`link`
  6. JOIN `poll_answer` ON `data_link`.`id` = `poll_answer`.`id`
  7. LEFT JOIN `data_link` AS `data_link_2` ON `poll_answer`.`id` = `data_link_2`.`link`
  8. LEFT JOIN `user` ON `data_link_2`.`id` = `user`.`id`
  9.  
  10. WHERE `data`.`status` != 'D'
  11. AND (`user`.`id` != 3 OR `user`.`id` IS NULL);


Now this kinda works, but it still returns every `poll`'s id instead of only the ones that the `user` hasn't voted on. The reason for this is simple, but I can't find a solution: The query joins the `poll_answer`s to the `poll` and the `user` to the `poll_answer`, and then I simply get it where the `user`s `id` isn't linked to an answer. The problem is, each `poll` has more than one possible `poll_answer` so it still returns the answers that the user isn't linked to and thus returns the `poll`'s id. I can't link the `user` to all the `poll_answer`s as then wouldn't know what the `user` voted.

I can either link the `user` to the `poll` when he/she votes and then just check on that, or I could alter the query to get the correct results. I don't know how to alter the query to get what I need so I'd like to learn how to do that, maybe one of you could assist me here?
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9090
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

I read through this a few times, very confusing. Would it be possible to post the results of this query, and then post the results that you want to see if the query was correct? That might help illustrate exactly what you are looking for.

Post Information

  • Total Posts in this topic: 2 posts
  • Users browsing this forum: Liamw411 and 51 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.