I have two tables and wanted to make a join and extracting the target output. but I'm not getting the desired results as shown in the image.

My query:

select  
    a.catalog_number,
    a.catalog_image,
    b.catalog_description 
from 
    catalog_type a
inner join
    catalog_details b on a.catalog_number = b.catalog_number

Comments: I should get 3 rows but the query returns 6 rows based on the join condition.

Thanks Rakesh

CodePudding user response：

I see many problems on your question.

1. The tables named on the query are not the same on the picture, so i don't know if you write wrong the query or your image is not right.
2. If i replace the table "catalog_details" from the query with catalog_description from image the total rows returned will be 9 no 6.

So, your problem is in the model, or your conception about the model and the data, not in the query.

To get something like the result that you show on the image, you need some like this.

CodePudding user response：

alter table Catalog_Type add Catalog_Type_ID int identity(1,1)

alter table Catalog_description add Catalog_Description_ID int identity(1,1)

select ct.catalag_number, ct.catalog_image,cd.catalog_description 
from Catalog_Type as ct 
inner join Catalog_description cd 
on cd.Catalog_Description_ID = ct.Catalog_Type_ID 

Add a column to existing table and uniquely number them on MS SQL Server

You can check in this link how to add Id column to table.