List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:May 31 2006 5:33pm
Subject:Re: where more than 2 column not in a table
View as plain text  
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 
>
>
>   

Attachment: [text/html]
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
Thread
where more than 2 column not in a tableif0402929 May
  • Re: where more than 2 column not in a tablePeter Brawley29 May
    • Re: where more than 2 column not in a tableif0402930 May
      • Re: where more than 2 column not in a tablePeter Brawley31 May