View help needed 
Author Message
 View help needed

I have a query I'm trying to build. I have 2 tables, as follows:

TableA
fielda
fieldb
fieldc

TableB
fielda
fieldb
fieldc

I want to run a query where talbea.fielda = tableb.fielda, and tablea.fieldb
= tableb.fieldb (does that make sense?) and tablea.fielda = a given value.

My sql looks like this:
Select tablea.fieldc, tableb.fieldc;
from tablea;
left join tableb;
on tablea.fielda = tableb.fielda;
left join tableb;
on tablea.fieldb = tableb.fieldb;
where tablea.fielda = lcValue

This gives me 40,000 identical records.

What am I missing? Why does it give me all these identical records instead
of the record I want? And more importantly, how would I put this into the
view designer? There is only one record in tablea where fielda = lcvalue.
There are 2 fields used to relate tablea into tableb.



Wed, 08 Dec 2004 12:05:57 GMT  
 View help needed
Unless I misunderstand ...

Select tablea.fieldc, tableb.fieldc;
from tablea;
left join tableb;
on tablea.fielda = tableb.fielda;
AND tablea.fieldb = tableb.fieldb;
where tablea.fielda = lcValue

HTH

--
Roger Ansell



Quote:
> I have a query I'm trying to build. I have 2 tables, as follows:

> TableA
> fielda
> fieldb
> fieldc

> TableB
> fielda
> fieldb
> fieldc

> I want to run a query where talbea.fielda = tableb.fielda, and tablea.fieldb
> = tableb.fieldb (does that make sense?) and tablea.fielda = a given value.

> My sql looks like this:
> Select tablea.fieldc, tableb.fieldc;
> from tablea;
> left join tableb;
> on tablea.fielda = tableb.fielda;
> left join tableb;
> on tablea.fieldb = tableb.fieldb;
> where tablea.fielda = lcValue

> This gives me 40,000 identical records.

> What am I missing? Why does it give me all these identical records instead
> of the record I want? And more importantly, how would I put this into the
> view designer? There is only one record in tablea where fielda = lcvalue.
> There are 2 fields used to relate tablea into tableb.



Wed, 08 Dec 2004 19:29:12 GMT  
 View help needed
This won't work if there is no matching record in tableb. That is why I was
using the left join, so that I would get the record in tablea, regardless of
whether or not there are records in tableb. Do I need to concatenate the key
fields here? Maybe I need an index on fielda + fieldb?


Quote:
> Unless I misunderstand ...

> Select tablea.fieldc, tableb.fieldc;
> from tablea;
> left join tableb;
> on tablea.fielda = tableb.fielda;
> AND tablea.fieldb = tableb.fieldb;
> where tablea.fielda = lcValue

> HTH

> --
> Roger Ansell



> > I have a query I'm trying to build. I have 2 tables, as follows:

> > TableA
> > fielda
> > fieldb
> > fieldc

> > TableB
> > fielda
> > fieldb
> > fieldc

> > I want to run a query where talbea.fielda = tableb.fielda, and
tablea.fieldb
> > = tableb.fieldb (does that make sense?) and tablea.fielda = a given
value.

> > My sql looks like this:
> > Select tablea.fieldc, tableb.fieldc;
> > from tablea;
> > left join tableb;
> > on tablea.fielda = tableb.fielda;
> > left join tableb;
> > on tablea.fieldb = tableb.fieldb;
> > where tablea.fielda = lcValue

> > This gives me 40,000 identical records.

> > What am I missing? Why does it give me all these identical records
instead
> > of the record I want? And more importantly, how would I put this into
the
> > view designer? There is only one record in tablea where fielda =
lcvalue.
> > There are 2 fields used to relate tablea into tableb.



Thu, 09 Dec 2004 04:40:24 GMT  
 View help needed


Quote:
> This won't work if there is no matching record in tableb. That is why
> I was using the left join, so that I would get the record in tablea,
> regardless of whether or not there are records in tableb. Do I need
> to concatenate the key fields here? Maybe I need an index on fielda +
> fieldb?

EM,

Roger has given you the correct solution as you have described your
problem. Did you try his code? I've written it out so you can try it. If
it's not what you want then please add more rows to illustrate.

CREATE CURSOR TableA (FieldA I, FieldB I, FieldC I)
INSERT INTO TableA VALUES (1, 2, 1)
INSERT INTO TableA VALUES (2, 2, 2)
INSERT INTO TableA VALUES (2, 3, 2)
INSERT INTO TableA VALUES (3, 2, 3)

CREATE CURSOR TableB (FieldA I, FieldB I, FieldC I)
INSERT INTO TableB VALUES (1, 2, 1)
INSERT INTO TableB VALUES (2, 2, 2)
INSERT INTO TableB VALUES (3, 3, 3)

*!* Separate indexes on FieldA and FieldB
*!* in each table will speed this query

lnValue = 2
SELECT ;
 TableA.FieldC AS AC, ;
 TableB.FieldC AS BC ;
 FROM TableA ;
 LEFT JOIN TableB ON ;
 TableA.FieldA = TableB.FieldA AND ;
 TableA.FieldB = TableB.FieldB ;
 WHERE TableA.FieldA = lnValue

--

Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



Thu, 09 Dec 2004 07:56:42 GMT  
 View help needed


Quote:


> > This won't work if there is no matching record in tableb. That is why
> > I was using the left join, so that I would get the record in tablea,
> > regardless of whether or not there are records in tableb. Do I need
> > to concatenate the key fields here? Maybe I need an index on fielda +
> > fieldb?

> EM,

> Roger has given you the correct solution as you have described your
> problem. Did you try his code? I've written it out so you can try it. If
> it's not what you want then please add more rows to illustrate.

> CREATE CURSOR TableA (FieldA I, FieldB I, FieldC I)
> INSERT INTO TableA VALUES (1, 2, 1)
> INSERT INTO TableA VALUES (2, 2, 2)
> INSERT INTO TableA VALUES (2, 3, 2)
> INSERT INTO TableA VALUES (3, 2, 3)

> CREATE CURSOR TableB (FieldA I, FieldB I, FieldC I)
> INSERT INTO TableB VALUES (1, 2, 1)
> INSERT INTO TableB VALUES (2, 2, 2)
> INSERT INTO TableB VALUES (3, 3, 3)

> *!* Separate indexes on FieldA and FieldB
> *!* in each table will speed this query

> lnValue = 2
> SELECT ;
>  TableA.FieldC AS AC, ;
>  TableB.FieldC AS BC ;
>  FROM TableA ;
>  LEFT JOIN TableB ON ;
>  TableA.FieldA = TableB.FieldA AND ;
>  TableA.FieldB = TableB.FieldB ;
>  WHERE TableA.FieldA = lnValue

> --

> Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

> http://msdn.microsoft.com/vfoxpro  http://foxcentral.net

Cindy,

I tried it in my application, and I got no results. Your sample code above
does work, I'm not sure why it didn't work in my app. I went ahead and
modified my table structure to eliminate the need to do a join on 2 fields,
as it simplified a few other things as well. Thanks for the help!

-Em-



Thu, 09 Dec 2004 08:21:38 GMT  
 View help needed


Quote:
> I tried it in my application, and I got no results. Your sample code
> above does work, I'm not sure why it didn't work in my app. I went
> ahead and modified my table structure to eliminate the need to do a
> join on 2 fields, as it simplified a few other things as well. Thanks
> for the help!

Sometimes settings like EXACT and ANSI affect how comparisons are made -
whether any rows are found to be equal to your parameter. These are
among the first things to suspect when SQL gives unexpected results.

--

Cindy Winegarden  MCSD, Microsoft Visual FoxPro MVP

http://msdn.microsoft.com/vfoxpro  http://foxcentral.net



Thu, 09 Dec 2004 12:12:13 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Need help with Views

2. Need help with views and grids

3. Need more view help!

4. I NEED HELP I NEED HELP CUSTOMIZING EXCHANGE 5.0

5. HELP,HELP,HELP need help fast

6. I Need Help needed generating a special report!!!

7. Help needed with ONLINE HELP system inside .EXE file

8. Need Help For Customize Help In Foxpro for Dos 2.5

9. (*.HLP) : Need help to build Windows 3.x Help Files

10. Need more help with HELP!!!!!!

11. Help Newibe needs Basic help

12. HELP! NOVICE NEEDS VFP 5 REPORT HELP

 

 
Powered by phpBB® Forum Software