IF in Query

[Norbert Pfeiffer]

Hi Guenther,

Habe Dir mein Beispiel unten drunter geschrieben.
Brauchst nur kopieren, und sofort auszuprobieren.
Es werden immer die langen Vornamen ausgegeben.

Sollte es mit Deiner Realitaet nicht ganz harmonieren,
musst Du halt doch einen Muster-Dump anfertigen.

Geht nicht - gibts nicht - sonst alles ... ;-)


m. b. G. Norbert
_____________________
normal:  02292-681769
Notruf:  0177-2363368
-----------------------------------------------------------
USE test;

DROP TABLE IF EXISTS A;
CREATE TABLE A (
  id      int(11) NOT NULL auto_increment,
  name    varchar(55) NOT NULL,
  PRIMARY KEY (id)
) TYPE=MyISAM;

DROP TABLE IF EXISTS B;
CREATE TABLE B (
  id      int(11) NOT NULL auto_increment,
  a_id    int(11) NOT NULL,
  name    varchar(55) NOT NULL,
  PRIMARY KEY (id)
) TYPE=MyISAM;

INSERT INTO A VALUES (1, 'Friedrich Schmidt');
INSERT INTO A VALUES (2, 'Rorberto Mueller');
INSERT INTO A VALUES (3, 'Bernhard Kaiser');

INSERT INTO B VALUES (1, 3, 'B. Kaiser');
INSERT INTO B VALUES (2, 0, 'Wolfgang Schulze');
INSERT INTO B VALUES (3, 1, 'F. Schmidt');
INSERT INTO B VALUES (4, 0, 'Rickardo Maier');
INSERT INTO B VALUES (5, 2, 'R. Mueller');

SELECT
IF ((b.a_id = 0), (b.name), (a.name)) AS Name
FROM a LEFT JOIN b ON (b.id = 1) WHERE
IF ((b.a_id = 0), (b.id = 1), (b.id = 1 AND a.id = b.a_id))
GROUP BY b.id
;
SELECT
IF ((b.a_id = 0), (b.name), (a.name)) AS Name
FROM a LEFT JOIN b ON (b.id = 2) WHERE
IF ((b.a_id = 0), (b.id = 2), (b.id = 2 AND a.id = b.a_id))
GROUP BY b.id
;
SELECT
IF ((b.a_id = 0), (b.name), (a.name)) AS Name
FROM a LEFT JOIN b ON (b.id = 3) WHERE
IF ((b.a_id = 0), (b.id = 3), (b.id = 3 AND a.id = b.a_id))
GROUP BY b.id
;
SELECT
IF ((b.a_id = 0), (b.name), (a.name)) AS Name
FROM a LEFT JOIN b ON (b.id = 4) WHERE
IF ((b.a_id = 0), (b.id = 4), (b.id = 4 AND a.id = b.a_id))
GROUP BY b.id
;
SELECT
IF ((b.a_id = 0), (b.name), (a.name)) AS Name
FROM a LEFT JOIN b ON (b.id = 5) WHERE
IF ((b.a_id = 0), (b.id = 5), (b.id = 5 AND a.id = b.a_id))
GROUP BY b.id
;
-----------------------------------------------------------
e.o.m.


---
Infos zur Mailingliste, zur Teilnahme und zum An- und Abmelden unter
-->>  http://www.4t2.com/mysql