SQL Select – Complex Query ??

[cdms]

I am using SQL select in VFP6 and I have two tables with a “One to Many” relationship. Person.dbf & Codes.dbf

I want to select all the people from the person table that have both code A1 and code A2 on the code table. The problem I have is that on the Code table these two codes would be on two different records (One record per code).

I know I can Query on the individual codes building two cursors and then inner join them back to the person table but this seems massively long-winded.

Does anyone have any ideas about a good way to do this baring in mind I will probably be querying massively more complex codes than the example above?

Appreciate the help in advance

Chris

 

[ramani]

HI

USE PERSONS IN 0 ALIAS persons
USE codes IN 0 ALIAS code1
USE codes AGAIN IN 0 ALIAS code2

SELECT myRequiredFields FROM persons, code1, code2 ;
WHERE persons.code1 = code1.codefield AND ;
persons.code2 = code2.codeField
etc etc.. usual construction..

The key is to OPEN THE TABLES usins AGAIN class and use teh ALIAS to make your SQL.

Hope this helps you ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[fluteplr]

You do not have to do the table uses first.

SELECT myRequiredFields FROM persons, codes code1, codes code2 ;
WHERE persons.code1 = code1.codefield AND ;
persons.code2 = code2.codeField

 

[cdms]

Thanks guys I'll give it a try

Chris

 

[cdms]

Hi guys

Just got round to testing that code and i'm having a few problems. Below is what I am writing :

SELECT * ;
FROM PERSON P INNER JOIN CODE C1 INNER JOIN CODE C2 ;
ON PERSON.PERSON_NO = C2.PERSON_NO ;
ON PERSON.PERSON_NO = C1.PERSON_NO ;
INTO CURSOR CODERESULTS ;
WHERE (C1.PROD_GRP == "10&quot AND (C2.PROD_GRP == "20&quot

I don't seem to be selecting a lot

I am proberbly making a silly keying error. Could you set me right.

Thanks

Chris

 

[ramani]

Hi

1. DOnt use C1, C2 etc in your SELECT statement to reference the Tables.. They simply stand for your work area reference and not valid.
Again..
ON PERSON.PERSON_NO = C2.PERSON_NO ;
ON PERSON.PERSON_NO = C1.PERSON_NO
WHat is your point in using the same field comparison. You have not compared CodeA and CodeB instead of Person_no OR Person_no and CodeA or CodeB variation.


2. Next.. After thoughts.. instead of above method, you can try the following..

SELECT A.* FROM PERSON A, Code B INTO CURSOR myCursor ;
WHERE A.codeA = B.CodeA AND ;
A.CodeB IN (SELECT A.CodeB FROM PERSON A, ;
Code b WHERE A.CodeB = b.codeB)

This type of a construct should help you out.

ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[cdms]

Ramani

I don’t think I explained myself well enough in my first query (Sorry). There is not a code record on the person table. There is a unique identifier field that is called person_no. On the codes table the field person_no is the "MANY". On the code table there is a field called prod_grp eg.

Person table
person_no = "00000001" Name = "Chris"
person_no = “00000002” Name = “Ramani”
Codes Table
person_no = "00000001" prod_grp = "10"
person_no = "00000001" prod_grp = "92"
person_no = "00000001" prod_grp = "23"
person_no = "00000002" prod_grp = "15"
person_no = "00000002" prod_grp = "92"
person_no = "00000002" prod_grp = "13"
person_no = "00000002" prod_grp = "93"

***** code example *******
USE ISQQPERSON IN 0 ALIAS PERS
USE ISQQCODE IN 0 ALIAS CODE1
USE ISQQCODE AGAIN IN 0 ALIAS CODE2

SELECT PERS.* ;
FROM PERS INNER JOIN CODE1 INNER JOIN CODE2 ;
ON PERS.PERSON_NO = CODE2.PERSON_NO ;
ON PERS.PERSON_NO = CODE1.PERSON_NO ;
INTO CURSOR CODERESULTS ;
WHERE (CODE1.PROD_GRP == "92&quot AND (CODE2.PROD_GRP == "93&quot
****** end code ***********

In the above example above I would want to select “Ramani” only.

I am proberbly going to be selecting 10 or 20 codes in one selection. Can you help ?

Regards

Chris