How to select only 1 row if multiple duplicate one id in another table

Question

I have 3 tables :

1-Contracts -contracts_id -subject_contract -case_id

contracts_id  |  subject_contract  |  case_id
001           |  name              |  01
002           |  name              |  02
003           |  name              |  01

2-contracts_files -contracts_id -file_data

contracts_id  |  file_data
001           |  image <varbinary(MAX)>
002           |  image <varbinary(MAX)>
001           |  image <varbinary(MAX)>
002           |  image <varbinary(MAX)>
003           |  image <varbinary(MAX)>
003           |  image <varbinary(MAX)>

3-Cases -case_id -case_name

case_id  |  case_name
01       |  case one
02       |  case two

Need output in dataGridView like this :

contracts_id  |  subject_contract  |  case_name  |  file_data  |  file_data 
001           |  name              |  case one   |  image      |  image 
002           |  name              |  case two   |  image      |  image 
003           |  name              |  case one   |  image      |  image

get all images have one contracts_id in one row (if contract 001 have 2 images get them in one row)

I try this :

create proc GET_ALL_CONTRACTS
as
SELECT contracts.[contracts_id]
      ,[subject_contract]
      ,[case_name] 
      ,[file_data]
FROM contracts, contracts_files
  Contracts INNER join Cases
  ON Cases.case_id = Contracts.case_id 
where contracts_files.contracts_id = Contracts.contracts_id

What if contract 001 is having 3 images ? Do you want another column in that case ? — Pரதீப், Jun 22 '17 at 04:45
@NorbertvanNobelen "on one row" means in two separate columns. See the sample output, how there's two "file_data" columns. — ZLK, Jun 22 '17 at 04:53
You need `dynamic pivot`. Check this question https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query Start with static pivot, If you find any difficulty in achieving it. Post whatever you have tried I will write the answer.. — Pரதீப், Jun 22 '17 at 04:58
Here is my pivot from yesterday : https://stackoverflow.com/questions/44663798/swapping-rows-as-a-columns/44664077#comment76313916_44664077 — jdweng, Jun 22 '17 at 05:08
@jdweng - Which one will be better in terms of performance `Sql` or `C#` ? — Pரதீப், Jun 22 '17 at 05:15
SQL is better searching through lot of data, but formatting results the language is poor. Also comparison has to consider the amount of data being returned from SQL to c#. In your case where you may have 1000 images that you want to filter to 50 it is better to do in SQL because the images are large and transfer of large amount of data will reduce performance. But once the duplicates are removed the pivot is probably better in c#. Once the list object created in c# using linq will use pointer to the cells so object won't have to move during the query, just the pointer will change. — jdweng, Jun 22 '17 at 05:36

score 0 · Accepted Answer · answered Jun 22 '17 at 16:41

I think I had a similar case with one of my projects that I've worked on.

What you need to do is to combine all columns from multiple tables into one table using stored procedure and foreigner keys. With making sure that you get the unique columns first then link the records with them.

Let's take your schema as an example, we will create a new table and name it ContractsMain and it'll have these columns:

Now, we will create a stored procedure that will get the records from each table to be inserted into each column. Example :

ContractID = ( SELECT ContractsMain.ContractsID 
               FROM ContractsMain
               INNER JOIN Contracts ON Contracts_ID = ContractsMain.ContractsID       ),

SubjectContract = ( SELECT ContractsMain.SubjectContract
                    FROM ContractsMain
                   INNER JOIN Contracts ON Contracts_ID = ContractsMain.ContractsID   ),

So, you'll do the same method on each column with adding your functions and conditions on each one of them, and let the procedure UPDATE and ALTER the table on your conditions.

Then you create a new view and add ContractsMain table, and select the columns you need to view.

Any new view that needs new columns which ContractsMain doesn't have, you simple add the columns into ContractsMain and repeat the above process.

I do that mostly on Reports and Views that needs more than two tables. In which will keep the records organized and more readable in one place. Leaving the main tables and records aside and untouched, and if there is any change in main tables (new records, modifications ..etc) this will automatically will be updated in the ContractsMain.

How to select only 1 row if multiple duplicate one id in another table

1 Answers1