File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes How to not include Employee ID predicate when it is not available Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to not include Employee ID predicate when it is not available" Watch "How to not include Employee ID predicate when it is not available" New topic
Author

How to not include Employee ID predicate when it is not available

Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 590

There could be times when e.HandledBy is a null column, how do I make the ResultSet visible
even e.HandedBy is not available?
Thanks
Jack
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

Are you sure your current query returns the result you want?

Firstly, you have a group by, but don't specify aggregation functions for most of the selected columns. Some databases don't even allow it. MySQL apparently does, but I'm not sure how the non-aggregated values are resolved.

Secondly, you've thrown in a customer table, which is not joined to any other table, and actually not used at all. This causes the records from the elog and employee tables to be repeated for every record in the customer table (a cartesian product of all rows is produced), and I suspect that you've thrown in the group by just to get rid of the multiple rows that are generated due to the customer table being included. But this is just guessing and I may be wrong. What did you want to do with the customer table?

Assuming the above is fixed, the operation you want to use is called an outer join. This is one of the basic operations in SQL, so I'd suggest to start by googling sql outer join tutorial. Feel free to ask specific questions if you encounter further problems.
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 590
Martin Vajsar wrote:Are you sure your current query returns the result you want?

Firstly, you have a group by, but don't specify aggregation functions for most of the selected columns. Some databases don't even allow it. MySQL apparently does, but I'm not sure how the non-aggregated values are resolved.

Secondly, you've thrown in a customer table, which is not joined to any other table, and actually not used at all. This causes the records from the elog and employee tables to be repeated for every record in the customer table (a cartesian product of all rows is produced), and I suspect that you've thrown in the group by just to get rid of the multiple rows that are generated due to the customer table being included. But this is just guessing and I may be wrong. What did you want to do with the customer table?

Assuming the above is fixed, the operation you want to use is called an outer join. This is one of the basic operations in SQL, so I'd suggest to start by googling sql outer join tutorial. Feel free to ask specific questions if you encounter further problems.


Thanks Martin,
I have created a subquery with a left join, and eliminated the unnecessary parameters.
Seems to work, but have to test later
Jack
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to not include Employee ID predicate when it is not available
 
Similar Threads
using dt and dt to make expandable / collapsable tree
aggregate function using alias name and fetch the result into the resultset using the Alias name
recursive JQL/HQL
Tapestry JavaBeans across pages
Hibernate Delete Not Null Columns