General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
Try this:
Select a.PrimaryKey, a.DataItem ;
From TableA a Inner Join TableB b On a.PrimaryKey = b.ForeignKey ;
Where b.Condition = .T. ;
Group by a.PrimaryKey, a.DataItem
Have indexes on TableA.PrimaryKey, TableB.ForeignKey and TableB.Condition
In SQL it's usually best to use "group by" with a condition since the grouping will occur only on the items that meet the condition, this is fewer records to look at which means, normally, faster results. The "distinct" approach scans all values since the parser can only know what is distinct by looking at all of the records.
>Not having strong experience with SQL, I need a little help to get the right result set for a new view.
>
>TableA: PrimaryKey(I), DataItem(C 20)
> 1, Apples
> 2, Oranges
> 3, Pears
> 4, Bananas
>TableB: ForeignKey(I), Condition(L)
> 1, .T.
> 1, .T.
> 2, .F.
> 4, .T.
> 4, .T.
> 4, .F.
>
>Result: PrimaryKey(I), DataItem(C 20)
> 1, Apples
> 4, Bananas
>(One result from Table A only if there's a True Condition in table B.)
>
>Normally, I'd try to join the tables and use DISTINCT PrimaryKey, or GROUP BY PrimaryKey. Shouldn't those give the same result? Which is better? Does VFP8's change to GROUP BY syntax change this? Would an EXISTS clause (which I've never used before) work?
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only