Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic