Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: Jun Tangunan
  Where is Jun Tangunan?
 Cabanatuan
 Philippines
 Jun Tangunan
 Tags
Subject: RE: INLIST IN SELECT STATEMENT
Thread ID: 249029 Message ID: 249048 # Views: 1 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Sunday, December 27, 2009 3:24:11 PM         
   


> >
> > This is a trivial case of having a lookup table that describes the office organozation. It should not be solved by various tricky or simple queries. Any change in he office organization could then be handled simply by modifiying a data table, instead of rewriting code.
 
> > OPEN DATABASE xxx
> > CREATE TABLE StaffCategories (catcode C(6) PRIMARY KEY, descript C(20))
> > INSERT INTO  Staffcategories VALUES ('STF001','SENIOR STAFF') 
> > INSERT INTO  Staffcategories VALUES ('STF002','SENIOR STAFF') 
> > INSERT INTO  Staffcategories VALUES ('STF003','SENIOR STAFF') 
> > INSERT INTO  Staffcategories VALUES ('STF004','JUNIOR STAFF') 
> > INSERT INTO  Staffcategories VALUES ('STF005','JUNIOER STAFF') 
> > INSERT INTO  Staffcategories VALUES ('STF006','CLERICAL STAFF') 
> > INSERT INTO  Staffcategories VALUES ('STF007','CLERICAL STAFF') 
> > 
> > SELECT T1.*, T2.descript ;
> >  FROM Mydata T1 LEFT JOIN Staffcategories T2 ON T1.catcode=T2.catcode ;
> >  INTO CURSOR Query1 

> >
> > Even better would be to put the three category description names into three rows in a table:
> >  CREATE TABLE Categories (catno N(2) PRIMARY KEY, name C(20) )
> >  INSERT INTO Categories VALUES (1, 'Senior staff')
> >  INSERT INTO Categories VALUES (2, 'Junior staff')
> >  INSERT INTO Categories VALUES (1, 'Clerical staff') 

> >
> > and change StaffCategories this way:
> >  CREATE TABLE Catcodes (catcode C(6) PRIMARY KEY, catno N(2) REFERENCES Categories)
> >  INSERT INTO  Catcodes VALUES ('STF001',1) 
> >  INSERT INTO  Catcodes VALUES ('STF002',1) 
> >  INSERT INTO  Catcodes VALUES ('STF003',1) 
> >  INSERT INTO  Catcodes VALUES ('STF004',2) 
> >  INSERT INTO  Catcodes VALUES ('STF005',2) 
> >  INSERT INTO  Catcodes VALUES ('STF006',3) 
> >  INSERT INTO  Catcodes VALUES ('STF007',3) 

> >
> > And use this query:
> >  SELECT T1.* , T2.name FROM Mydata T1 ;
> >  JOIN Catcodes T0 ON T0.catcode=T1.catcode ;
> >  JOIN Categories T2 ON T2.catno=T0.catno   

> >
> > -Anders
>
>
> Hi Anders,
>
> I am actually waiting for you to jump in as I am sure that the answer will be great as always. I never thought of creating another table (T2) in this case, which I will defenitely do in my end when I need something like that. :-)
>
> However, I believe you have a typo here:
>
>
INSERT INTO Categories VALUES (1, 'Clerical staff')
> 

>
> Should be?
>
>
INSERT INTO Categories VALUES (3, 'Clerical staff')
> 

>
> Jun Tangunan
> http://weblogs.foxite.com/sandstorm36/default.aspx
> http://www.coderisland.com/forum/viewforum.php?f=10

Hi Jun, good eye. It would have thron an error 'Uniqueness violated' anyway as it was marked as primary key.

-Anders

ENTIRE THREAD

INLIST IN SELECT STATEMENT Posted by DEREK DODOO @ 12/27/2009 10:12:30 AM
RE: INLIST IN SELECT STATEMENT Posted by Jun Tangunan @ 12/27/2009 10:34:45 AM
RE: INLIST IN SELECT STATEMENT Posted by M. Akram Bhatti @ 12/27/2009 10:47:17 AM
RE: INLIST IN SELECT STATEMENT Posted by Jun Tangunan @ 12/27/2009 10:56:32 AM
RE: INLIST IN SELECT STATEMENT Posted by M. Akram Bhatti @ 12/27/2009 11:02:41 AM
RE: INLIST IN SELECT STATEMENT Posted by Jun Tangunan @ 12/27/2009 11:07:19 AM
RE: INLIST IN SELECT STATEMENT Posted by M. Akram Bhatti @ 12/27/2009 10:53:26 AM
RE: INLIST IN SELECT STATEMENT Posted by Anders Altberg @ 12/27/2009 11:46:42 AM
RE: INLIST IN SELECT STATEMENT Posted by Jun Tangunan @ 12/27/2009 11:54:25 AM
RE: INLIST IN SELECT STATEMENT Posted by Anders Altberg @ 12/27/2009 3:24:11 PM
RE: INLIST IN SELECT STATEMENT Posted by kulwant singh @ 12/27/2009 3:39:46 PM
RE: INLIST IN SELECT STATEMENT Posted by tushar @ 12/27/2009 4:06:32 PM