[MySQL] query vergemakkelijken * - Softwareontwikkeling

woensdag 21 januari 2004 02:07

Acties:

Verwijderd

Topicstarter

Ik heb de volgende 2 tabellen:

tabel_een:

code:

tabel_een_id | naam
-------------+----------
           1 | naam-a
           2 | naam-b
           3 | naam-c
           4 | naam-d
           5 | naam-e
           6 | naam-f
           7 | naam-g
           8 | naam-h
           9 | naam-i

tabel_twee

code:

id | tabel_een_id
---+--------------
 1 | 2
 2 | 3
 3 | 6
 4 | 7
 5 | 1
 6 | 9

Hoe maak ik het makkelijkse een opsomming van alle namen uit tabel_een die niet in tabel_twee voorkomen.
nu doe ik zoiets van:

code:

counter1 = 0;
counter2 = 0;
SELECT tabel_een_id, naam FROM tabel_een;
  counter1++;
  loop ->
    SELECT id FROM tabel_twee WHERE tabel_een_id = '<tabel_een_id>'
    if (id != "") {
      counter2++;
    else
      zet het tabel_een_id in een array
    fi
done

laat alle tabel_een_id's uit de array zien;

Ik denk dat dit vast wel vele malen makkelijker in 1 sql query kan?
En hoe kan ik het aantal herkennen? nu kan ik een sizeof(array) doen of iets met counter1 en counter2 doen.

woensdag 21 januari 2004 02:15

Acties:

RobIII

Admin Devschuur®

^ Romeinse Ⅲ ja!

SQL:

1
2
3

Select naam from tabel_een where tabel_een.tabel_een_id not in (
   Select tabel_twee.tabel_een_id from tabel_twee
)

Zou moeten werkenWerkt _{effe getest} in MSSQL als ik me niet vergis. Maar je vermeldt er niet bij welke SQL je gebruikt...

En als je het aantal wil hebben:

SQL:

1
2
3

Select count(1) from tabel_een where tabel_een.tabel_een_id not in (
   Select tabel_twee.tabel_een_id from tabel_twee
)

[ Voor 37% gewijzigd door RobIII op 21-01-2004 02:22 ]

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Je eigen tweaker.me redirect

Over mij

woensdag 21 januari 2004 02:25

Acties:

Verwijderd

Topicstarter

ik gebruik mysql, ga het even proberen

woensdag 21 januari 2004 02:32

Acties:

RobIII

Admin Devschuur®

^ Romeinse Ⅲ ja!

Ik doe nooit geen ene plork met MySQL, maar heb effe snel voor je gekeken en volgens de MySQL manual zou het moeten kunnen...

Overigens is het ook mogelijk met een join. Misschien wel net zo mooi...

Succes!

[ Voor 28% gewijzigd door RobIII op 21-01-2004 02:34 ]

There are only two hard problems in distributed systems: 2. Exactly-once delivery 1. Guaranteed order of messages 2. Exactly-once delivery.

Je eigen tweaker.me redirect

Over mij

woensdag 21 januari 2004 02:44

Acties:

Verwijderd

Topicstarter

de join werkt perfect, thx!

de IN statements werken (schijnbaar?) alleen vanaf mysql 4.1 welke ik niet tot m'n beschikking heb.

woensdag 21 januari 2004 03:22

Acties:

Verwijderd

Topicstarter

hoe kan ik met een join een conditie voor tabel_een EN tabel_twee meegeven?

alleen een conditie voor tabel_een meegeven lukt me:

code:

mysql> select * from tabel_een;
+--------------+--------+----------+
| tabel_een_id | naam   | conditie |
+--------------+--------+----------+
|            1 | naam-a | optie1   |
|            2 | naam-b | optie1   |
|            3 | naam-c | optie1   |
|            4 | naam-d | optie2   |
|            5 | naam-e | optie2   |
|            6 | naam-f | optie3   |
|            7 | naam-g | optie4   |
|            8 | naam-h | optie1   |
|            9 | naam-i | optie3   |
+--------------+--------+----------+
9 rows in set (0.00 sec)

mysql> select * from tabel_twee;
+------+--------------+
| id   | tabel_een_id |
+------+--------------+
|    1 |            2 |
|    2 |            3 |
|    3 |            6 |
|    4 |            7 |
|    5 |            1 |
|    6 |            9 |
+------+--------------+
6 rows in set (0.00 sec)

mysql> SELECT tabel_een.* FROM tabel_een 
LEFT JOIN tabel_twee ON tabel_een.tabel_een_id=tabel_twee.tabel_een_id 
WHERE tabel_twee.tabel_een_id IS NULL;
+--------------+--------+----------+
| tabel_een_id | naam   | conditie |
+--------------+--------+----------+
|            4 | naam-d | optie2   |
|            5 | naam-e | optie2   |
|            8 | naam-h | optie1   |
+--------------+--------+----------+
3 rows in set (0.00 sec)

mysql> SELECT tabel_een.* FROM tabel_een 
LEFT JOIN tabel_twee ON tabel_een.tabel_een_id=tabel_twee.tabel_een_id 
WHERE tabel_twee.tabel_een_id IS NULL AND tabel_een.conditie = 'optie1';
+--------------+--------+----------+
| tabel_een_id | naam   | conditie |
+--------------+--------+----------+
|            8 | naam-h | optie1   |
+--------------+--------+----------+
1 row in set (0.00 sec)

mysql> SELECT tabel_een.* FROM tabel_een 
LEFT JOIN tabel_twee ON tabel_een.tabel_een_id=tabel_twee.tabel_een_id 
WHERE tabel_twee.tabel_een_id IS NULL AND tabel_twee.id > '4';
Empty set (0.00 sec)

de conditie voor tabel_twee pakt hij dus niet op, komt dat omdat ik daar in de linkste select geen select op tabel_twee doe?

woensdag 21 januari 2004 07:37

Acties:

robjanssen

Software Developer

Zet je conditie meteen achter de LEFT JOIN met een AND

code:

1
2
3

SELECT tabel_een.*
FROM tabel_een
LEFT JOIN tabel_twee ON tabel_een.tabel_een_id=tabel_twee.tabel_een_id AND tabel_twee.tabel_een_id IS NULL;

[ Voor 16% gewijzigd door robjanssen op 21-01-2004 07:37 ]

woensdag 21 januari 2004 13:15

Acties:

Verwijderd

Topicstarter

Het werkt nu, ik plak hier de uitkomst nog even mocht er iemand nog naar willen kijken.

ziet hieronder (met nieuwe waarden in andere structuur van de tabellen:

code:

mysql> SELECT * FROM tabel_een;
+--------------+--------+----------+
| tabel_een_id | naam   | conditie |
+--------------+--------+----------+
|            1 | naam-a | optie1   |
|            2 | naam-b | optie1   |
|            3 | naam-c | optie1   |
|            4 | naam-d | optie2   |
|            5 | naam-e | optie2   |
|            6 | naam-f | optie3   |
|            7 | naam-g | optie4   |
|            8 | naam-h | optie1   |
|            9 | naam-i | optie3   |
+--------------+--------+----------+
9 rows in set (0.01 sec)

mysql> SELECT * FROM tabel_twee;
+------+--------------+-----------+
| id   | tabel_een_id | conditie2 |
+------+--------------+-----------+
|    1 | 2            | optie1    |
|    2 | 3            | optie1    |
|    3 | 6            | optie1    |
|    4 | 7            | optie1    |
|    5 | 1            | optie1    |
|    6 | 9            | optie1    |
|    7 | 4            | optie2    |
|    8 | 5            | optie2    |
+------+--------------+-----------+
8 rows in set (0.00 sec)

Laat alles uit tabel_een zien waarvan 'tabel_een_id' niet voorkomt in 'tabel_twee':

code:

mysql> SELECT tabel_een.* FROM tabel_een
    -> LEFT JOIN tabel_twee ON tabel_een.tabel_een_id=tabel_twee.tabel_een_id AND tabel_twee.conditie2='optie1'
    -> WHERE tabel_twee.tabel_een_id IS NULL;
+--------------+--------+----------+
| tabel_een_id | naam   | conditie |
+--------------+--------+----------+
|            4 | naam-d | optie2   |
|            5 | naam-e | optie2   |
|            8 | naam-h | optie1   |
+--------------+--------+----------+
3 rows in set (0.00 sec)

Laat alles uit tabel_een zien waarvan 'tabel_een_id' niet voorkomt in 'tabel_twee' met de voorwaarde dat conditie2 = 'optie2':

code:

mysql> SELECT tabel_een.* FROM tabel_een
    -> LEFT JOIN tabel_twee ON tabel_een.tabel_een_id=tabel_twee.tabel_een_id AND tabel_twee.conditie2='optie2'
    -> WHERE tabel_twee.tabel_een_id IS NULL;
+--------------+--------+----------+
| tabel_een_id | naam   | conditie |
+--------------+--------+----------+
|            1 | naam-a | optie1   |
|            2 | naam-b | optie1   |
|            3 | naam-c | optie1   |
|            6 | naam-f | optie3   |
|            7 | naam-g | optie4   |
|            8 | naam-h | optie1   |
|            9 | naam-i | optie3   |
+--------------+--------+----------+
7 rows in set (0.00 sec)

Laat alles uit tabel_een zien waarvan 'tabel_een_id' niet voorkomt in 'tabel_twee' met de voorwaarde dat conditie2 = 'optie3', in dit geval bestaat optie3 niet dus alle 'tabel_een_id's komen niet voor in tabel_twee:

code:

mysql> SELECT tabel_een.* FROM tabel_een
    -> LEFT JOIN tabel_twee ON tabel_een.tabel_een_id=tabel_twee.tabel_een_id AND tabel_twee.conditie2='optie3'
    -> WHERE tabel_twee.tabel_een_id IS NULL;
+--------------+--------+----------+
| tabel_een_id | naam   | conditie |
+--------------+--------+----------+
|            1 | naam-a | optie1   |
|            2 | naam-b | optie1   |
|            3 | naam-c | optie1   |
|            4 | naam-d | optie2   |
|            5 | naam-e | optie2   |
|            6 | naam-f | optie3   |
|            7 | naam-g | optie4   |
|            8 | naam-h | optie1   |
|            9 | naam-i | optie3   |
+--------------+--------+----------+
9 rows in set (0.01 sec)

en met een conditie op tabel_een en een conditie op tabel_twee:

code:

mysql> SELECT tabel_een.* FROM tabel_een
    -> LEFT JOIN tabel_twee ON tabel_een.tabel_een_id=tabel_twee.tabel_een_id AND tabel_twee.conditie2='optie3'
    -> WHERE tabel_twee.tabel_een_id IS NULL AND tabel_een.conditie = 'optie2';
+--------------+--------+----------+
| tabel_een_id | naam   | conditie |
+--------------+--------+----------+
|            4 | naam-d | optie2   |
|            5 | naam-e | optie2   |
+--------------+--------+----------+
2 rows in set (0.01 sec)

maandag 26 januari 2004 02:41

Acties:

Verwijderd

Topicstarter

ik ga nog even verder

Ik heb de volgende tabellen:

code:

CREATE TABLE tabel1 (
  mid INT(5) DEFAULT '0' NOT NULL,
  gid INT(5) DEFAULT '0' NOT NULL
);

CREATE TABLE tabel2 (
  pid int(8) NOT NULL auto_increment,
  mid INT(5) DEFAULT '0' NOT NULL,
  pub ENUM ('0','1') DEFAULT '1',
  PRIMARY KEY (pid)
);

CREATE TABLE tabel3 (
  t3_id int(5) NOT NULL auto_increment,
  gid INT(5) DEFAULT '0' NOT NULL,
  pid INT(5) DEFAULT '0' NOT NULL,
  PRIMARY KEY (t3_id)
);

INSERT INTO tabel1 (mid, gid) VALUES ('3','1');
INSERT INTO tabel1 (mid, gid) VALUES ('1','2');
INSERT INTO tabel1 (mid, gid) VALUES ('2','2');
INSERT INTO tabel1 (mid, gid) VALUES ('3','2');
INSERT INTO tabel1 (mid, gid) VALUES ('2','4');
INSERT INTO tabel1 (mid, gid) VALUES ('4','4');

INSERT INTO tabel2 (pid, mid, pub) VALUES ('1','1','1');
INSERT INTO tabel2 (pid, mid, pub) VALUES ('2','1','1');
INSERT INTO tabel2 (pid, mid, pub) VALUES ('3','2','1');
INSERT INTO tabel2 (pid, mid, pub) VALUES ('4','3','1');
INSERT INTO tabel2 (pid, mid, pub) VALUES ('5','3','0');
INSERT INTO tabel2 (pid, mid, pub) VALUES ('6','3','1');
INSERT INTO tabel2 (pid, mid, pub) VALUES ('7','3','1');
INSERT INTO tabel2 (pid, mid, pub) VALUES ('8','3','0');
INSERT INTO tabel2 (pid, mid, pub) VALUES ('9','4','0');
INSERT INTO tabel2 (pid, mid, pub) VALUES ('10','4','1');
INSERT INTO tabel2 (pid, mid, pub) VALUES ('11','4','1');
INSERT INTO tabel2 (pid, mid, pub) VALUES ('12','4','0');
INSERT INTO tabel2 (pid, mid, pub) VALUES ('13','4','1');

INSERT INTO tabel3 (t3_id, gid, pid) VALUES ('1','1','6');
INSERT INTO tabel3 (t3_id, gid, pid) VALUES ('2','1','8');
INSERT INTO tabel3 (t3_id, gid, pid) VALUES ('3','1','9');
INSERT INTO tabel3 (t3_id, gid, pid) VALUES ('4','1','4');
INSERT INTO tabel3 (t3_id, gid, pid) VALUES ('5','2','2');
INSERT INTO tabel3 (t3_id, gid, pid) VALUES ('6','2','3');
INSERT INTO tabel3 (t3_id, gid, pid) VALUES ('7','3','6');
INSERT INTO tabel3 (t3_id, gid, pid) VALUES ('8','3','7');
INSERT INTO tabel3 (t3_id, gid, pid) VALUES ('9','3','8');
INSERT INTO tabel3 (t3_id, gid, pid) VALUES ('10','4','2');
INSERT INTO tabel3 (t3_id, gid, pid) VALUES ('11','4','4');
INSERT INTO tabel3 (t3_id, gid, pid) VALUES ('12','4','10');
INSERT INTO tabel3 (t3_id, gid, pid) VALUES ('13','4','12');

Daarmee gebruik ik het volgende script:

code:

$counter = 0;
$gid = 4;

$sql = "SELECT mid FROM tabel1 WHERE gid = '".$gid."'";
$result = mysql_query ($sql);
if ($row = mysql_fetch_array($result)) {
    do {
        $mid = $row["0"];

        $sql2 = "SELECT pid FROM tabel2 WHERE mid = '".$mid."' AND pub = '1'";
        $result2 = mysql_query ($sql2);
        if ($row2 = mysql_fetch_array($result2)) {
            do {
                $pid = $row2["0"];

                $sql3 = "SELECT t3_id FROM tabel3 WHERE gid = '".$gid."' AND pid = '".$pid."'";
                $result3 = mysql_query ($sql3);
                $row3 = mysql_fetch_array($result3);
                $t3_id = $row3["0"];
                if ($t3_id == "") {
                    // komt niet voor in tabel 3
                    $counter++;
                    print ($pid." komt niet voor<br>\n");
                }
            } while ($row2 = mysql_fetch_array($result2));
        }
    } while ($row = mysql_fetch_array($result));
}
print ("gid ".$gid." : totaal: ".$counter."<br><br>\n");

Dit wil ik nu omvormen naar 1 query, maar dat wil me niet lukken. Het bovenstaande script heeft als output:

code:

3 komt niet voor
11 komt niet voor
13 komt niet voor
gid 4 : totaal: 3

wat vanzelfsprekend als je het script nagaat ook correct is:

de pid's 3, 11, 13 uit tabel1 komen niet in tabel 3 voor, met in acht nemend dat de mids van deze pids wel in tabel2 moeten staan en de betreffende pid's ook de waarde 1 moeten hebben voor 'pub'.

Nu probeer ik hier een join constructie van de maken en naar wat ik kan redeneren kom ik met de volgende query:

code:

SELECT tabel2.pid FROM tabel2
LEFT JOIN tabel1 ON tabel1.mid = tabel2.mid AND tabel1.mid IS NOT NULL
LEFT JOIN tabel3 ON tabel2.pid = tabel3.pid
WHERE tabel3.pid IS NULL AND tabel2.pub = '1' AND tabel1.gid = '".$gid."';

(vul $gid = 4; in)

redenatie:
laat alle pids zien waarbij:
- de mids uit tabel 1 en tabel 2 overeenkomen en deze in tabel 1 WEL aanwezig zijn
- de pids uit tabel 2 NIET in tabel 3 voorkomen
- waarbij pub uit tabel 2 de waarde '1' heeft en
- waarbij gid = '4' in tabel 1

als ik deze query uitvoer krijg ik als output:

code:

mysql> SELECT tabel2.pid FROM tabel2
    -> LEFT JOIN tabel1 ON tabel1.mid = tabel2.mid AND tabel1.mid IS NOT NULL
    -> LEFT JOIN tabel3 ON tabel2.pid = tabel3.pid
    -> WHERE tabel3.pid IS NULL AND tabel2.pub = '1' AND tabel1.gid = '4'; 
+-----+
| pid |
+-----+
|  11 |
|  13 |
+-----+

Waar is nummertje 3 gebleven en waar gaat mijn redenatie dus ook fout?

Pagina: 1

Reageer