Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 6 of 6 total |
help with a join query |
Thu, Jul 25 2013 8:34 AM | Permanent Link |
Luis Conception Gonzalez | Hi!
I have two tables with the following data: Table1 (places) CODE NAME 1 Place1 2 Place2 3 Place 3 4 Place 4 Table2 (quantity) PLACE PRODUCT QUANTITY 1 A 3 2 A 2 1 B 5 3 B 10 I need to show quantity by place for ONE product (for example A), showing ALL places, not only the ones where product A has quantity. I mean, I want to show this: PLACENAME QUANTITY Place1 3 Place2 2 Place3 Place4 ....even if there is no quantity on Place3 and 4 I need to show them. I have been trying different JOIN clauses with no success. Is this possible? Thanks! Luis C. |
Thu, Jul 25 2013 8:52 AM | Permanent Link |
Raul Team Elevate | What about something like this:
select * from Table1 LEFT OUTER JOIN Table2 on Table1.CODE=Table2.Place Where Table2.PRODUCT=1 OR Table2.PRODUCT is NULL Raul On 7/25/2013 8:34 AM, Luis Conception Gonzalez wrote: > Hi! > > I have two tables with the following data: > > Table1 (places) > CODE NAME > 1 Place1 > 2 Place2 > 3 Place 3 > 4 Place 4 > > Table2 (quantity) > PLACE PRODUCT QUANTITY > 1 A 3 > 2 A 2 > 1 B 5 > 3 B 10 > > I need to show quantity by place for ONE product (for example A), > showing ALL places, not only the ones where product A has quantity. > I mean, I want to show this: > > PLACENAME QUANTITY > Place1 3 > Place2 2 > Place3 > Place4 > > ...even if there is no quantity on Place3 and 4 I need to show them. > > I have been trying different JOIN clauses with no success. Is this > possible? > > Thanks! > > Luis C. |
Thu, Jul 25 2013 9:28 AM | Permanent Link |
Raul Team Elevate | I meant this (.PRODUCT='A' in where clause)
select * from Table1 LEFT OUTER JOIN Table2 on Table1.CODE=Table2.Place Where Table2.PRODUCT='A' OR Table2.PRODUCT is NULL Raul |
Fri, Jul 26 2013 5:50 AM | Permanent Link |
Luis Conception Gonzalez | I already tried this. No success.
I'm getting this result: PLACENAME QUANTITY Place1 3 Place2 2 Place3 "Place4" is not shown. Maybe because there is no Place4 at all on Table2 Luis C. "Raul" escribió en el mensaje de noticias:C6122010-3E3B-4A75-BDA8-84BBA5DFBB99@news.elevatesoft.com... I meant this (.PRODUCT='A' in where clause) select * from Table1 LEFT OUTER JOIN Table2 on Table1.CODE=Table2.Place Where Table2.PRODUCT='A' OR Table2.PRODUCT is NULL Raul |
Fri, Jul 26 2013 6:41 AM | Permanent Link |
Jose Eduardo Helminsky HPro Informatica | After checking your need...
select code,name,if(product='A','A',null),if(product='A',qty,0) from Table1 left JOIN Table2 on Table1.CODE=Table2.Place or table2.product is null group by code,name Eduardo |
Mon, Jul 29 2013 4:29 AM | Permanent Link |
John Hay | Luis
How about select * from places LEFT OUTER JOIN quantity on places.CODE=quantity.Place and quantity.PRODUCT='A' John |
This web page was last updated on Monday, May 6, 2024 at 12:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |