select * from product p
inner join category_subcategories c
where c.category_name = 'Electronic';

@NamedQuery(name = "ProductCategory.findProductJoinByCategoryName", query =
        "select p from ProductCategoryAssociate p "
        + "where p.productCategoryAssociatePK.categoryId = p.productCategoryAssociatePK.productId "
        + "and p.categorySubcategories.category_name = :categoryName")

public List<Product> findAllProductsByCategoryName(String categoryName) {
        Query q = em.createNamedQuery("ProductCategory.findProductJoinByCategoryName");
        q.setParameter("categoryName", categoryName);
        try {
          return (List<Product>) q.getResultList();
        } catch (NoResultException e) {
          return null;
        }
}

package au.com.houseware.server.ejb.entity;

@Entity
@Table(name = "product_category_associate", catalog = "houseware", schema = "")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "ProductCategoryAssociate.findAll", query = "SELECT p FROM ProductCategoryAssociate p"),
    @NamedQuery(name = "ProductCategoryAssociate.findByProductId", query = "SELECT p FROM ProductCategoryAssociate p WHERE p.productCategoryAssociatePK.productId = :productId"),
    @NamedQuery(name = "ProductCategoryAssociate.findByCategoryId", query = "SELECT p FROM ProductCategoryAssociate p WHERE p.productCategoryAssociatePK.categoryId = :categoryId"),
    @NamedQuery(name = "ProductCategoryAssociate.findByTotalItems", query = "SELECT p FROM ProductCategoryAssociate p WHERE p.totalItems = :totalItems"),
    @NamedQuery(name = "ProductCategory.findProductJoinByCategoryName", query =
        "select p from ProductCategoryAssociate p "
        + "where p.productCategoryAssociatePK.categoryId = p.productCategoryAssociatePK.productId "
        + "and p.categorySubcategories.category_name = :categoryName")
})
public class ProductCategoryAssociate implements Serializable {
    private static final long serialVersionUID = 1L;
    @EmbeddedId
    protected ProductCategoryAssociatePK productCategoryAssociatePK;
    @Column(name = "total_items")
    private Integer totalItems;
    @JoinColumn(name = "category_id", referencedColumnName = "category_id", nullable = false, insertable = false, updatable = false)
    @ManyToOne(optional = false)
    private CategorySubcategories categorySubcategories;
    @JoinColumn(name = "product_id", referencedColumnName = "product_id", nullable = false, insertable = false, updatable = false)
    @ManyToOne(optional = false)
    private Product product;

    public ProductCategoryAssociate() {
    }

    public ProductCategoryAssociate(ProductCategoryAssociatePK productCategoryAssociatePK) {
        this.productCategoryAssociatePK = productCategoryAssociatePK;
    }

    public ProductCategoryAssociate(int productId, int categoryId) {
        this.productCategoryAssociatePK = new ProductCategoryAssociatePK(productId, categoryId);
    }

    public ProductCategoryAssociatePK getProductCategoryAssociatePK() {
        return productCategoryAssociatePK;
    }

    public void setProductCategoryAssociatePK(ProductCategoryAssociatePK productCategoryAssociatePK) {
        this.productCategoryAssociatePK = productCategoryAssociatePK;
    }

    public Integer getTotalItems() {
        return totalItems;
    }

    public void setTotalItems(Integer totalItems) {
        this.totalItems = totalItems;
    }

    public CategorySubcategories getCategorySubcategories() {
        return categorySubcategories;
    }

    public void setCategorySubcategories(CategorySubcategories categorySubcategories) {
        this.categorySubcategories = categorySubcategories;
    }

    public Product getProduct() {
        return product;
    }

    public void setProduct(Product product) {
        this.product = product;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (productCategoryAssociatePK != null ? productCategoryAssociatePK.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof ProductCategoryAssociate)) {
            return false;
        }
        ProductCategoryAssociate other = (ProductCategoryAssociate) object;
        if ((this.productCategoryAssociatePK == null && other.productCategoryAssociatePK != null) || (this.productCategoryAssociatePK != null && !this.productCategoryAssociatePK.equals(other.productCategoryAssociatePK))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "au.com.houseware.server.ejb.entity.ProductCategoryAssociate[ productCategoryAssociatePK=" + productCategoryAssociatePK + " ]";
    }

}

@NamedQuery(name = "ProductCategory.findProductJoinByCategoryName", query =
        "select p from ProductCategoryAssociate p "
        + "where p.productCategoryAssociatePK.categoryId = p.categorySubcategories.categoryId "
        + "and p.productCategoryAssociatePK.productId = p.product.productId "
        + "and p.categorySubcategories.category_name = :categoryName")

public void findByInnerJoinCategory_categoryName(String categoryName) {
        Session sess = null;
        try{
          SessionFactory fact = new
          Configuration().configure().buildSessionFactory();
          sess = fact.openSession();
         
          String sql_query = "select p.product_name from product p inner join category_subcategories c where c.category_name = 'Electronic'";
          //String sql_query = "select d.name,p.name,sum(p.price) as totalprice from Product  p join p.dealer d group by p.name";
            org.hibernate.Query query = sess.createQuery(sql_query);
         
          for(Iterator it=
            query.iterate();it.hasNext();){
                    Object[] row = (Object[]) it.next();
                    System.out.print(row[0]);
                    System.out.print("\t\t"+row[1]);
                    System.out.print("\t"+row[2]);
                    System.out.println();
              }
            sess.close();
            }
            catch(Exception e ){
              System.out.println(e.getMessage());
            }
       
    }

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
   <persistence-unit name="HousewareEnterpriseApplication-v7-ejbPU" transaction-type="RESOURCE_LOCAL">
      <properties>
         <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
         <property name="hibernate.hbm2ddl.auto" value="update"/>
         <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
         <property name="hibernate.connection.username" value="root"/>
         <property name="hibernate.connection.password" value="admin"/>
         <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/houseware"/>
      </properties>
   </persistence-unit>
</persistence>

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD//EN"
    "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <!-- Database connection settings -->
         <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
      <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/houseware</property>
      <property name="hibernate.connection.username">root</property>
      <property name="hibernate.connection.schema">houseware</property>
      <property name="hibernate.connection.password">admin</property>
      <property name="hibernate.connection.pool_size">10</property>
      <property name="hibernate.connection.autommit">true</property>
      <property name="show_sql">true</property>

      <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
      <property name="hibernate.hbm2ddl.auto">update</property>

        <!-- Mapping files -->
        <mapping resource="product.hbm.xml"/>
        <mapping resource="category_subcategories.hbm.xml"/>
    </session-factory>
</hibernate-configuration>

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-mapping
   PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
   <class name="CategorySubcategories" table="houseware.category_subcategories">
      <id name="category_id" type="int" column="categoryId"/>

      <property name="category_name" type="string" column="categoryName"/>

     <property name="category_id" type="int" column="categoryId"/>
   
      <one-to-many name="product" class="Product" column="productId"/>

   </class>

 
</hibernate-mapping>

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-mapping
   PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
   <class name="Product" table="houseware.product">
      <id name="product_id" type="int" column="productId"/>

      <property name="product_name" type="string" column="productName"/>

     <property name="product_desc" type="string" column="productDesc"/>
     <property name="product_code" type="string" column="productCode"/>
     <property name="category_id" type="int" column="categoryId"/>
      <property name="factory_price" type="double" column="factroyPrice"/>
      <many-to-one name="category_subcategories" class="CategrySubcategories" column="categoryId"/>

   </class>

   <query name="HQLcategoryName"><![CDATA[
       select from Product p inner join CategorySubcategories c where c.category_name = 'Electronic']]>
    </query>   
   <sql-query name="SQLpricing">
      <return-scalar column="price" type="double"/>
      <![CDATA[
       select product.factory_price from Product as product where product.factory_price > 25.0]]>
   </sql-query>     
</hibernate-mapping>