From: Peter Brawley Date: May 31 2006 5:33pm Subject: Re: where more than 2 column not in a table List-Archive: http://lists.mysql.com/mysql/198472 Message-Id: <447DD378.8030607@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-447DD37952A8=======" --=======AVGMAIL-447DD37952A8======= Content-Type: multipart/alternative; boundary=------------000801080700090000050209 --------------000801080700090000050209 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit if04029@stripped wrote: >I want to select a list, where it was registered in tbl club_get_match with Id- >Member in tbl detail_club_member, but it's not registered in tbl wager. Your schema has a problem: wagers.name_club is too short. If no club_get_match.name_club value is longer than 20 chars, this should work: SELECT * FROM club_get_match c INNER JOIN detail_club_member m USING (name_club) LEFT JOIN wager ON c.name_club=w.name_club WHERE w.name_club IS NULL; For a brief explanation see 'The [Not] Exists Query Pattern' at http://www.artfulsoftware.com/queries.php. PB > CREATE TABLE `club_get_match` ( > `id_match` bigint(100) NOT NULL default '0', > `name_club` varchar(30) NOT NULL default '', > `status` varchar(30) default NULL, > `prediction_match` varchar(5) NOT NULL default '', > `equal_match` char(3) NOT NULL default '', > `score` varchar(50) default NULL, > `scorerr` varchar(10) default NULL, > PRIMARY KEY (`id_match`,`name_club`) > ); > > CREATE TABLE `detail_club_member` ( > `id_member` bigint(20) NOT NULL default '0', > `choose` varchar(50) NOT NULL default '', > `name_club` varchar(30) NOT NULL default '', > PRIMARY KEY (`id_member`,`name_club`) > ); > > CREATE TABLE `wager` ( > `id_match` bigint(100) NOT NULL default '0', > `id_member` varchar(8) NOT NULL default '', > `total_wager` bigint(10) NOT NULL default '0', > `total_money` bigint(20) NOT NULL default '0', > `name_club` varchar(20) NOT NULL default '', > `time_update` datetime default '0000-00-00 00:00:00', > PRIMARY KEY (`id_match`,`id_member`,`name_club`) > ); > > I want to select a list, where it was registered in tbl club_get_match with Id- > Member in tbl detail_club_member, but it's not registered in tbl wager. > > > > > > > > > > > Quoting Peter Brawley : > > >> if04029@stripped wrote: >> >>> Sory if my question makes you confused >>> I've a problem with my sintax, >>> I don't know how to select a list, whether it's not registered in another >>> >>> table, but it have 2 IDs. >>> >>> >> Not enough info. Show the Create Table statements, a bit of data, and >> the desired result. >> >> PB >> >>> I hope you will help me because I really need it for my study. >>> >>> Thank you in advance, >>> >>> regards, >>> Eva Panjaitan >>> 11104029 >>> Student of Del Polytechnic of Informatics >>> Sitoluama - Laguboti >>> Toba Samosir >>> Sumatera Utara >>> >>> >>> >> -- >> No virus found in this outgoing message. >> Checked by AVG Free Edition. >> Version: 7.1.394 / Virus Database: 268.7.3/350 - Release Date: 5/28/2006 >> >> >> > > regards, > > Eva Panjaitan > 11104029 > Student of Del Polytechnic of Informatics > Sitoluama - Laguboti > Toba Samosir > Sumatera Utara > > > --------------000801080700090000050209 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit if04029@stripped wrote:

>I want to select a list, where it was registered in tbl club_get_match with Id-
>Member in tbl detail_club_member, but it's not registered in tbl wager.

Your schema has a problem: wagers.name_club is too short. If no club_get_match.name_club value is longer than 20 chars, this should work:

SELECT *
FROM club_get_match c
INNER JOIN detail_club_member m USING (name_club)
LEFT JOIN wager ON c.name_club=w.name_club
WHERE w.name_club IS NULL;

For a brief explanation see 'The [Not] Exists Query Pattern' at http://www.artfulsoftware.com/queries.php.

PB

CREATE TABLE `club_get_match` (
  `id_match` bigint(100) NOT NULL default '0',
  `name_club` varchar(30) NOT NULL default '',
  `status` varchar(30) default NULL,
  `prediction_match` varchar(5) NOT NULL default '',
  `equal_match` char(3) NOT NULL default '',
  `score` varchar(50) default NULL,
  `scorerr` varchar(10) default NULL,
  PRIMARY KEY  (`id_match`,`name_club`)
);

CREATE TABLE `detail_club_member` (
  `id_member` bigint(20) NOT NULL default '0',
  `choose` varchar(50) NOT NULL default '',
  `name_club` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`id_member`,`name_club`)
);

CREATE TABLE `wager` (
  `id_match` bigint(100) NOT NULL default '0',
  `id_member` varchar(8) NOT NULL default '',
  `total_wager` bigint(10) NOT NULL default '0',
  `total_money` bigint(20) NOT NULL default '0',
  `name_club` varchar(20) NOT NULL default '',
  `time_update` datetime default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id_match`,`id_member`,`name_club`)
);

I want to select a list, where it was registered in tbl club_get_match with Id-
Member in tbl detail_club_member, but it's not registered in tbl wager.










Quoting Peter Brawley <peter.brawley@stripped>:

if04029@stripped wrote:

Sory if my question makes you confused
I've a problem with my sintax,
I don't know how to select a list, whether it's not registered in another

table, but it have 2 IDs.

Not enough info. Show the Create Table statements, a bit of data, and 
the desired result.

PB

I hope you will help me because I really need it for my study.

Thank you in advance,

regards,
Eva Panjaitan
11104029 
Student of Del Polytechnic of Informatics 
Sitoluama - Laguboti 
Toba Samosir 
Sumatera Utara

-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.7.3/350 - Release Date: 5/28/2006


regards,

Eva Panjaitan
11104029 
Student of Del Polytechnic of Informatics 
Sitoluama - Laguboti 
Toba Samosir 
Sumatera Utara

--------------000801080700090000050209-- --=======AVGMAIL-447DD37952A8======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.8.0/353 - Release Date: 5/31/2006 --=======AVGMAIL-447DD37952A8=======--