Q about sql syntax

[Nifrabar]

Hi!

My view has among others an "property ID" (integer).

In a listbox I do make a selection of several property-id's
I retrieve e.g "12","25","30.
As it is a listbox these are all characters
of course the comma can be a digit as needed

Q:
What is syntax of this pseudo code:

Select * from myView where myView.nProperty = one of "12","25","30"

I know this can be done with subquery, but how?

TIA
-Bart

 

[Dan Freeman]

You're looking for IN:

SELECT * FROM myView WHERE ;
.nProperty IN "12","25","30"

 

[Mike Lewis]

Bart,

I think Dan has given you the right answer, except ...

What exactly is .nProperty? Is it a field within the view? If so, then you need to remove the dot in front of it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Nifrabar]

In addition:
I do have a table holding properties of people.
On a dataform you can give a person one or more properties.

This way three tables are involved:
- table holding properties
- table holding person data
- table holding many-many data

my goal is to have a query of people based upon certain properties.
that way I get all persons available having a property with eg ID 12 or ID 25 or ID 30.

I understand the given answer but how to generic get my selection (taken from listbox) into the SQL statement.
Would it be a solution to roll "12","25","30" into a var?
eg lcItems=["12","25","30"]

eg
select distinct name,street,pcd,place from myView into myCur where allt(str(nProperty)) in lcItems
distinct as people can have more than one property and I want them only once in the result
(It's a bit weird due to mixing up integers witch character value)
-Bart

 

[Olaf Doschke]

WHERE allt(str(nProperty)) in (&lcItems)

would work, it's jsut limited to a certain amount of IDs, IIRC 27.

You can always do an IN clause as an inner join, and I would prefer that, even if you seldom will need more than 27 IDs, because it avoids the macro substitution.

...FROM yourtable INNER JOIN joincursor on yourtable.ID = joincursor.ID

And then you just need to insert the selected IDs into the joincursor.

Bye, Olaf.

 

[Nifrabar]

Olaf,
Thanks for feedback.
With som trial on error I just found the solution with the macro substitution.
Especially the innerjoin I was looking for as this is imho more generic but due to my lack of knowledge on that part I needed you here.
Thanks again!
-Bart

 

[Olaf Doschke]

Well,

I already gave the syntax. As an extended sample:

create cursor joincursor (ID I)

*insert user selection in that cursor.

select distinct name,street,pcd,place from myView;
inner join joincursor on myView.ID = joincursor.ID;
into cursor myCur

Cursor creation of the joincursor wouldbe put at form.load or init perhaps. Filling in the user selections from the listbox into the cursor can be done as you like, you can also switch to a construct of two listboxes, moving selections from left to right, then the join cursor could also be the controlsource of the listbox displaying the selected items.

And then the SQL part is simply using the selections themselves to inner join and thereby filter the view records.

Bye, Olaf.

 

[wgcs]

Here's another variation:

create cursor joincursor (ID I)

*insert user selection in that cursor.

select name,street,pcd,place from myView;
where myView.ID in (select ID from joincursor);
into cursor myCur

- Bill

Get the best answers to your questions -- See FAQ481-4875.