Abfrage nicht normalisierter db (Datenbanken und Verzeichnisdienste)

[thread]12322[/thread]

Abfrage nicht normalisierter db

Leser: 1

esskar

2008-08-09 02:58

User since
2003-08-04
7321 Artikel
ModeratorIn

user image

scriptor

2008-08-09 03:38

User since
2008-05-07
69 Artikel
BenutzerIn

Code: (dl )

SELECT username 
FROM tabelle 
WHERE 
attribute != 'type' 
OR 
( attribute = 'type'  AND value != 'reject' )

Soweit es Deine Beschreibung hergab, natürlich ungetestet!

2008-08-09 03:40

User since
2003-08-04
12208 Artikel
Admin1

[sql]select
username, sum( if(attribute='type' and value='reject',1,0) ) as rejected
from foo group by username having rejected = 0;[/sql]
das geht bei mir...

Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. -- Damian Conway in "Perl Best Practices"
lesen: Wiki:

Wie frage ich & perlintro Wiki:

brian's Leitfaden für jedes Perl-Problem

2008-08-09 03:45

User since
2003-08-04
12208 Artikel
Admin1

scriptor+2008-08-09 01:38:15--

Code: (dl )

SELECT username 
FROM tabelle 
WHERE 
attribute != 'type' 
OR 
( attribute = 'type'  AND value != 'reject' )

das klappt nicht ganz =)
liefert:

Code: (dl )

+----------+
| username |
+----------+
| esskar   | 
| esskar   | 
| foo      | 
| foo      | 
| bar      | 
+----------+

Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. -- Damian Conway in "Perl Best Practices"
lesen: Wiki:

Wie frage ich & perlintro Wiki:

brian's Leitfaden für jedes Perl-Problem

esskar

2008-08-09 04:14

User since
2003-08-04
7321 Artikel
ModeratorIn

user image

@pg: danke.
habs mal mit dbix versucht

Code: (dl )

my $check = $c->model('FreeRadius')->resultset('Radcheck')
			->search( 
				{ }, 
				{ 
					select   => [ 'username', "SUM(if(attribute='auth-type' and value='reject',1,0)) as rejected" ],
					as       => [ 'username', 'rejected' ],
					group_by => \"username having rejected = 0"
				}
			);

da kommt aber nix bei rum ... irgendwie
in mysql selber sieht es gut aus

esskar

2008-08-09 04:14

User since
2003-08-04
7321 Artikel
ModeratorIn

user image

ah doch, danke @pq.

View all threads created 2008-08-09 02:58.