Question about SQL Statement VFP9SP2 1

[foxlover2012]

Hi All,
Question about an SQL statement and how to achieve this.
I want to show all records from table A and only certain records from B.

Table A - Master Inventory File (ALL ITEMS SHOULD SHOW)

Item #, Description, Price
38293, Paint, 9.99
29839, Brush, 3.99
38921, Bolt, .23

Table B - Order Detail Table (Only records matching an orderid will show)

Order ID, Qty Ordered, Item #
1000, 10, 29839

Basically I want my cursor to look like this
Result..
Item Number, QTY Ordered, Description, Price, Orderid
38293, Null, Paint, 9.99, NULL
29839, 10, Brush, 3.99, 1000
38921, Null, Bolt, .23, NULL

The kicker is that I want to show ALL items in table A (the main inventory table) even if they ARE NOT in the order file.
Basically what I want to achieve is the ability to see ALL items at the same time I can see items on the order.
Thanks for any help, John

 

[Mike Lewis]

John,

This sounds like a basic outer join. Something like this (off the top of my head):

SELECT ma.Item_Num, or.Qty, ma.Description, ma.Price, or.OrderID ;
  FROM Master ma;
  LEFT OUTER JOIN Orders or ON or.OrderID = ma.OrderID ;
  INTO CURSOR csrResults

Give that a try and see if it gives you what you want.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Indeed "the kicker" is not to also want dataa of a main table with no matches, Mike has that explained, that's the nature of an outer join, of which there are left and right outer joins, which tell of which table you want all records, no matter if there are matches.

A kicker would be, if you would want the orders aggregated, if there were multiple orders of a certain stock item.

As that's not (yet) the case, Mike should alreada have given the answer. But be prepared you could get several records per item number, if there were multiple orders, you don't get that aggregated. If you would want that aggregation, too, it wouldn't make sense to include an orderid though, as it can be none, one or multiple orders per item number.

Bye, Olaf.

 

[foxlover2012]

Yes, but I want to limit the order id to 1000 and if I do only records with that order id are included.
thanks.

 

[Olaf Doschke]

I think you haven't got my point, but if you need this for a single orderid anyway, it doesn't matter much.

Bye, Olaf.

 

[Mike Lewis]

Yes, but I want to limit the order id to 1000 and if I do only records with that order id are included.

I didn't see that in your original question.

But surely you can do it just by adding a WHERE clause:

SELECT ma.Item_Num, or.Qty, ma.Description, ma.Price, or.OrderID ;
  FROM Master ma;
  LEFT OUTER JOIN Orders or ON or.OrderID = ma.OrderID ;
  WHERE ISNULL(or.OrderID) OR or.OrderID = 1000 ;
  INTO CURSOR csrResults

Or is there something else I'm missing?

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Mike made one mistake, to join by orderid, you join by itemno of course.

And if you add a where clause of orderid=1000, you make an outer join an inner join, so don't put that into the where caluse, put it in the join condition.

That means overall:

SELECT ma.Item_Num, or.Qty, ma.Description, ma.Price, or.OrderID ;
  FROM Master ma;
  LEFT OUTER JOIN Orders or ON or.Itemno = ma.Item # And or.Orderid = 1000;
  INTO CURSOR csrResults

Bye, Olaf.

 

[Mike Lewis]

Quite right, Olaf. I was a bit hasty.

On reflection, I would also change the local alias from "or" to perhaps "ord". "Or" is probably OK, but it might also be confused with the OR operator.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

you can't have a field name Item # in DBF, though...

SELECT ma.Item_Num, or.Qty, ma.Description, ma.Price, or.OrderID ;
  FROM Master ma;
  LEFT OUTER JOIN Orders or ON or.Itemno = ma.Itemno And or.OrderID = 1000;
  INTO CURSOR csrResults

If you want to limit by some order field, you cannot do that in where, as that would drop all records without a join, as NULL is never fulfilling any specific filter clause for an orderid or order date range or whatever, put these conditions in the ON join condition.

Bye, Olaf.

 

[foxlover2012]

Awesome! It worked perfectly!

Thanks! John