buyanswerslinks — 2010-04-18T01:59:16-04:00 — #1
I have a peice of code that works fine but 1 part of it. I need it to select all support notes that are not flagged which
flagged = '0'. However, it is also pulling up flagged support notes as well, and flagged is
flagged = 1. So I am not sure what I did wrong if someone can help me out?
SELECT * FROM `notestrack` AS `notes1` WHERE `itemid` NOT IN (SELECT `itemid` FROM `notes` WHERE `usergroup` != 4 AND `flagged` = '0') AND id = (SELECT MAX(id) FROM `notes` WHERE notes1.itemid = `itemid`) ORDER BY `id` DESC
paul_wilkins — 2010-04-18T04:58:36-04:00 — #2
What type is the flagged field in your database? varchar, int, bool, etc...
If any field is integer or a bool, it should be unquoted.
buyanswerslinks — 2010-04-18T05:21:18-04:00 — #3
Thanks for the help, it is:
`flagged` smallint(1) NOT NULL default '0',
paul_wilkins — 2010-04-18T05:42:28-04:00 — #4
Okay, well try removing the quotes from all non-string values, and if you still need forther help with this we'll see what the good people in the MySQL forum can do to help with this.
guelphdad — 2010-04-18T14:41:21-04:00 — #5
quoted or unquoted won't make a difference, mysql will convert quoted string values.
show us some sample rows from your database, which ones are being returned and which of those that shouldn't be returned.
buyanswerslinks — 2010-04-18T19:26:53-04:00 — #6
Thanks for all your help, I still cant find out whats wrong, taking the "" away didnt do anything, below is an example row.
INSERT INTO `notessss` (`id`, `email`, `date`, `user`, `usergroup`, `url`, `itemid`, `question`, `flagged`) VALUES (1, 'firstname.lastname@example.org', '2009-03-16', 'testaccount', '2', 'http://www.firewaterrepair.com/', '400576', 'testy', 0),
paul_wilkins — 2010-04-18T19:35:16-04:00 — #7
Please share the column structure of notessss
guelphdad — 2010-04-18T22:55:13-04:00 — #8
Please don't post a single row as an insert statement. you need to show us several rows for us to determine what is wrong with your query. show us the rows and which ones are/are not being returned and why the should/shouldn't be returned.
a create table(s) statement with actual inserts (like you provided above) would help us to help you better.