I need some help on this Select sql Please

[Ernesto_Paez]

Hi Guys,
Let me explain, what i do i need to accomplish here, the Select SQL below actually do what i want but now, i got presented with a different issue.

when i type in the search text1 box for a value, if the item is common in both tables, then i can get a match and pull the Price from T2 but it could be the case that maybe, the item, does not exist in T2, for hence then, the result in the cursor, won't display, the item i was searching but the item exist in T1, so i need to still get the values from T1 and sent them to the cursor, in this case then what is going to happen is, that "T2.PRICE, won't be in the cursor ", ok so, what i need then if there is not matching, on item from both tables, to be able to send the searched item and the rest of the values of the fields from T1 to the cursor anyway, of course Price from T2, won't be there

can anyone help me on this SQL to add the code to accomplish it ?
or do i need to create a second Select sql for it in case the first one does not apply ?

I meant if the first sql, _tally=0, then i need to create a second Select sql, to just show the search item from T1 ?

Below is the SELECT SQl, i have in the txtbox interactivechange

Procedure Text1.InteractiveChange
   Local lcSearch
   lcSearch = Upper(Alltrim(This.Value))
   Thisform._cSearch = m.lcSearch
   path_1="f:\mfg\std\standard"
   path_2="f:\mfg\prices"
            
 select (t1.item) as itemno, (T1.draw_size) as DrawSize, (T1.prc) as PRC,;
 (T1.descrip) as description, (T1.descrip2) as Cross_Reference,;
 (T1.VPARTNO)as VendorPartNo,(T2.price) as Price,; 
 (T1.cost) as Cost,(T1.onhand) as On_Hand, (T1.orderpt) as OrderPoint,;
 (T1.orderqty) as OrderQty, (T1.onorder) as OnOrder,;
 (T1.order_date) as OrderDate, (T1.need_by)as NeededBy,;
(T1.allocated) as SetUp, (T1.run_avg) as Run ;
 from (path_1) T1 inner join(path_2) T2 on (T1.item) = (T2.item);
 WHERE (T1.item + T1.VPARTNO) LIKE '%'+lcSearch+'%'  into cursor Junkresult2  NOFILTER

  Select  Junkresult    
  Zap In Junkresult    
  Append From DBF('Junkresult2') 
  Go Top
  Thisform.grid1.Refresh
Endproc

Thanks in advance

 

[Olaf Doschke]

Well, then do a left join, not an inner join.

After doing that, take care T2 fields are NULL, if you don't find a join
So you might want [tt]NVL(T2.price,99999999.99) as Price[/tt] or NVL(T2.price,0.00) as Price. Or you stay with NULL and [tt]SET NULLDISPLAY TO ''[/tt] to get nothing displayed.
T2.price is the only field you take from T2, so it's the only thing to care for.

Bye, Olaf.

 

[Ernesto_Paez]

Yeah Olaf, that did it man, thanks a lot, now i know, the use if NVL() butat the same time, i was confused by the inner Join and left join, too, i am trying to read about these two, so i can understand they reason.

 

[Olaf Doschke]

In the first "approximation" the difference between inner and outer join, is the inner join is more strict and removes any t1 row not finding a similar item in t2 and vice versa, only matches remain, while the left join always takes all the left side (t1) data, no matter whether it finds a match in t2. the join is optional. There also is a right join, making the right side mandatory in the result and the left side of matches optional. Any right join becomes a left join by swutching the order of tables.

Both left and right joins are outer joins, the opposite of an inner join. Obviously demanding the matching condition has no mirrored secondary option, there is one inner join and two (left/right) outer joins. The left and right joins can also be written verbose as [tt]left outer join[/tt] and [tt]right outer join[/tt], but since there are no left or right inner joins the three typical notations in any SQL dialect are LEFT JOIN, RIGHT JOIN, and INNER JOIN. The main difference of being an inner vs outer join is lost to the irregular user of the language as ever so often, terminology gets shortened for the ones using it regularly.

Ok, and that said, there is an analogy to set operations. To see the usual depictions of this google "visual explanation of sql joins". Actually, honestly, you'll never remember this, if this isn't your regular work. This might be a nice memory aid, but the analogy has its limits. This only works for simpler queries on the one side, and there are several things you do on top of building intersections of sets or overlaps or a cut set, you combine elements of two or more sets, you have ordering, filtering, subqueries... All of sql capabilities hardly fits into the set operation analogy, though database tables are sets of similar elements, like the mathematical set analogy suggests, even in the simplest case you build intersections of sets of totally different elements, related elements like customers and orders, but the elements or entities are not as most often in mathematical sets just single numbers, they always are tuples of several fields with totally different, though typically related atomic informations. And maybe that's the major difference, you have very closely coupled related sets in database tables, you combine with queries to get a certain view and list of elements of a certain aspect. Mathematics also knows the same tuples but also sets of sets and other complex things, that don't fit into the scheme of databases, tables and columns. In the end the SQL stands for strcutured query language and not set query language.

Bye, Olaf.

 

[JRB-Bldr]

landfla - One pretty clear tutorial that I have found helpful which shows the various 'flavors' of JOIN (INNER, OUTER, etc.) may be found at:

http://www.datamartist.com/sql-inne...uter-join-examples-with-syntax-for-sql-server

Good Luck,
JRB-Bldr