aspose file tools*
The moose likes JDBC and the fly likes Want 27 records (3 customers * 9 age groups) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Want 27 records (3 customers * 9 age groups)" Watch "Want 27 records (3 customers * 9 age groups)" New topic
Author

Want 27 records (3 customers * 9 age groups)

Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 601
how to select age group data separated by product
I have 3 products´╝î9 age groups
want 27 results
using MariaDB


this only gets 9

AgeGroup
AgeGroupID AgeGroupName

Customer
CustomerID AgeGroupID

Orders
OrderID CustomerID

OrderDetails
OrderDetailID OrderID ProductID

Products
ProductID
Thanks
K. Tsang
Bartender

Joined: Sep 13, 2007
Posts: 2503
    
    8

You should use inner join not left join.


K. Tsang JavaRanch SCJP5 SCJD/OCM-JD OCPJP7 OCPWCD5 OCPBCD5
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19719
    
  20

Inner join would never give more results than a left or right outer join. What I think is required here is a cross join. That will take all records from the left table (3) and combine them all with all records from the right table (9). The result would be the intended 3 * 9 = 27 records.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Want 27 records (3 customers * 9 age groups)