Can't see the correct Result of Query with inner join

Question

I have 3 tables and I can't see the query result properly.

Following are the tables I'm using.

1. Project

2. Product

3. Users

I need Name from project and Ar_Description from Product to be displayed for the user logged. But the result of the query is showing only one result.

I want it to be grouped by the groups assigned to the user.

It is getting the correct user and group but not all the projects in that group.

Below is the code I'm using:

userLab =

 Select USERS.C_USER_LAB
 Where user_name = USER
 Order By user_name

usergroup =

  Select USERS.GROUP_NAME
  Where user_name = USER
  Order By user_name

Receive = "F"

cleararray(UnitsArray1)

Query ="Select DISTINCT PROJECT.NAME, PRODUCT.AR_DESCRIPTION, Project.GROUP_NAME from Project INNER JOIN PRODUCT ON PROJECT.C_SAMPLE_TYPE = PRODUCT.C_PRODUCT_TYPE where PROJECT.C_RECEIVED =  '" + Receive + "' and PROJECT.GROUP_NAME = '" + usergroup + "' and PROJECT.C_LAB = '" + userLab + "'"

Expected data:

Data Expected as different projects are there

Data received:

Same Project for different Description (All are not received or in project table)

Answer 1

This query will return same result as is in your second attached screen shot.

Select Project.ProjectName, Product.Ar_Description, Project.GROUP_NAME
 From Project
 inner join Product on Product.ProjectId = Project.ProjectId
 inner join Users U on U.Userid = Project.Userid
 Where U.Userid = 123
 and Project.GROUP_NAME = 'GDL_RY'
 group by Project.ProjectName,Product.Ar_Description,Project.GROUP_NAME

Let me create some logical understanding for you. Look, The expected output that you have attached in your first screen shot is fetchable but as you see, to get it you had to remove the Ar_Description column. But whenever you add the Product.Ar_Description Project repeats, why? because now the query will traverse through the Ar_Description column too and will add a row for each unique data.. Such as

1) Project1  has Careem in AR_Description
2) Project1 also has powder in AR_Description
hence 2 rows for Project1.

There are other ways and one of them is to get the count of Ar_Description or pivotize it.

for count:

Select Project.ProjectName, Count(Product.Ar_Description), Project.GROUP_NAME
 From Project
 inner join Product on Product.ProjectId = Project.ProjectId
 inner join Users U on U.Userid = Project.Userid
 Where U.Userid = 123
and Project.GROUP_NAME = 'GDL_RY'
 group by Project.ProjectName,Count(Product.Ar_Description)

result would be: Project1 has 100 AR_Descriptions Project2 has 1 AR_Descriptions

For Pivotize: see this answer : turn rows into columns