SQL QUERY PROBLEM & JOINS

[MForrest]

Im sure this is nothing new but I have tried all kind of joins and am very frustrated why my one to many relationship is gettin screwed up during my query. Heres my scenario:

I have 3 tables, Orders which are a 1 to many link by 'wo' to Orderlines and Clients which link to Orders by 'client_no'. I previously query my Orderline table to create a report by product code into cursor 'c_order_lines' I then need to query the table Orders to search for the accompanying records with the main order details for each order_line. When I do this my unique orders are duplicated within the cursor c_orders dependent upon how many order_lines there are thus messing up my report

Heres the code im using:
select orders.prefix, ;
orders.wo, ;
client.business, ;
orders.custno, ;
orders.location, ;
orders.accep_date, ;
orders.enqno ;
from client, orders right outer join c_order_lines ;
on orders.wo == c_order_lines.wo ;
where orders.client_no == client.client_no ;
order by client.business ;
into cursor c_orders

 

[baltman]

It might be as simple as adding a GROUP BY c_order_lines.wo
to the SQL, but make sure to check the results to make sure they are what they should be.

brian

 

[BPeisch]

When you have a one-to-many join in SQL statements, the results are de-normalized. That's the nature of SQL Select statements. If you only want one instance of the order header info, add a DISTINCT before you list the fields:

select DISTINCT ;
       orders.prefix, ;
       orders.wo, ;
       client.business, ;    
       orders.custno, ;
       orders.location, ;    
       orders.accep_date, ;
       orders.enqno ;
from client, orders right outer join c_order_lines ;
on orders.wo == c_order_lines.wo ;
where orders.client_no == client.client_no  ;    
order by client.business ;
into cursor c_orders

And BTW, the right outer join doesn't make sense in this case. You are telling VFP to include all records from c_order_lines, whether or not there's a match in orders, but you're not pulling any fields from c_order_lines or using them anywhere else. You should be using an inner join instead.


-BP (Barbara Peisch)

http://www.peisch.com

 

[MForrest]

Thank you Barbara, DISTINCT does the trick.

Pardon my ignorance I have been thrown in at the deep end Im a junior teaching myself foxpro. I will get there eventually.

 

[BPeisch]

Pardon my ignorance I have been thrown in at the deep end Im a junior teaching myself foxpro. I will get there eventually.

Yes, you will eventually get there. No problem. Just keep asking questions.




-BP (Barbara Peisch)

http://www.peisch.com