jasonc Posted December 5, 2008 Share Posted December 5, 2008 this works to show the lsit of surnames if there is more than one in any group. what i would like is to now reuse this but to have it now collect records where the 'status' of the user is 'confirmed' i have tried to place this is the code in variuous places but not getting very far. WHERE `status` = 'confirmed' and also tried AND `status` = 'confirmed' but not sure where these should go SELECT * FROM `table` WHERE `surname` IN (SELECT `surname` FROM `table` GROUP BY `surname` HAVING COUNT(*) > 1) ORDER BY `surname` Quote Link to comment Share on other sites More sharing options...
xtopolis Posted December 5, 2008 Share Posted December 5, 2008 SELECT * FROM `table` WHERE `surname` IN (SELECT `surname` FROM `table` GROUP BY `surname` HAVING COUNT(*) > 1) AND `status` = 'confirmed' ORDER BY `surname` would be my guess. Quote Link to comment Share on other sites More sharing options...
jasonc Posted December 5, 2008 Author Share Posted December 5, 2008 yep i tried that there and also somewhere else but forgot now. say the records are so... surname first name status smith john confirmed smith peter awaiting jones mary awaiting jones lisa confirmed jones simon confirmed should return..... jones lisa confirmed jones simon confirmed but say the records are.. smith john confirmed smith peter awaiting jones mary awaiting jones lisa confirmed jones simon awaiting it should return..... nothing but returns smith john confirmed smith peter awaiting jones mary awaiting jones lisa confirmed jones simon awaiting i need it to only return records where there are two or more records that have the same surname and that have also got the 'status' field set to 'confirmed' if there are only two records for say smith, one that is confirmed and the other that is not then none of these should be returned, but if there are say three or more and two of these have the status set to confirmed and have the same surname then just those two or more are returned. what sites can i go one that will go in great detail how this query is setup, as i really think if i could learn this method of quering i might be able to do this myself as i still trying to work out how i explain exactly what i am after. Quote Link to comment Share on other sites More sharing options...
fenway Posted December 5, 2008 Share Posted December 5, 2008 The query posted earlier should work. Quote Link to comment Share on other sites More sharing options...
jasonc Posted December 5, 2008 Author Share Posted December 5, 2008 it works but if say there are only two results, both are say 'smith' and one is 'confirmed' but the other is 'awaiting' it will stil show the 'confirmed' record, i only want records where both the surname is the same and if there are two that also both or all the records shown also have the status set to confirmed if one is confirmed but the other record is awaiting it should not show either of them, unless there is another record with the same surname and also has the status as confirmed Quote Link to comment Share on other sites More sharing options...
fenway Posted December 5, 2008 Share Posted December 5, 2008 Then add a WHERE clause to the subselect. Quote Link to comment Share on other sites More sharing options...
jasonc Posted December 5, 2008 Author Share Posted December 5, 2008 what site can i learn this on Quote Link to comment Share on other sites More sharing options...
fenway Posted December 5, 2008 Share Posted December 5, 2008 what site can i learn this on This one... there are hundreds of resources posted in the stickes on this board. For now, try this: SELECT * FROM `table` WHERE `surname` IN (SELECT `surname` FROM `table` WHERE `status` = 'confirmed' GROUP BY `surname` HAVING COUNT(*) > 1) AND `status` = 'confirmed' ORDER BY `surname` Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.