get data from two sql server table

Question

Post reply

get data from two sql server table

silbahi

SSC Enthusiast

Points: 190
More actions
December 17, 2020 at 2:59 pm

#3821618

Hello,
I have two tables in my DB sql Server :
Table1: LIST_POSTES columns : PK_POSTE , NAME_POSTE
PK_POSTE NAME_POSTE
----------- --------
1 Poste1
2 Poste2

Table 2: PARAMS_LIST_POSTES columns: FK_POSTE,NAME_PARAM, VALUE_PARAM
FK_POSTE NAME_PARAM VALUE_PARAM
----------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------
1 PathPrinter path 1
1 NamePrinter name imp 1
2 PathPrinter path 2
2 NamePrinter name imp 2
when calling a strored procedure, I want to display in my gridView a data like this:
NAME_POSTE NamePrinter PathPrinter
----------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------
Poste1 name imp 1 path 1
Poste2 name imp 2 path 2
My stored procedure :

CREATE PROCEDURE MyPROC
AS
BEGIN

DECLARE @Nom Varchar(50)
DECLARE @Valeur Varchar(100)

DECLARE @QUERY as NVARCHAR(4000)
DECLARE @QUERY2 as NVARCHAR(4000)
set @QUERY2=''

SET @QUERY = 'SELECT NAME_POSTE '

DECLARE CURSOR_Params CURSOR FORWARD_ONLY DYNAMIC
FOR SELECT [NAME_PARAM],[VALUE_PARAM]
FROM [PARAMS_LIST_POSTES]

OPEN CURSOR_Params

FETCH NEXT FROM CURSOR_Params
INTO @Nom,@Valeur

WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
SET @QUERY2=@QUERY2+','''+@Valeur+''' As '''+@Nom+''' '
END

FETCH NEXT FROM CURSOR_Params
INTO @Nom,@Valeur

END
CLOSE CURSOR_Params
DEALLOCATE CURSOR_Params

EXEC ( @QUERY + @Query2 + ' FROM [LIST_POSTES ]
INNER JOIN [PARAMS_LIST_POSTES] ON [LIST_POSTES ].PK_POSTE = [PARAMS_LIST_POSTES].FK_POSTE')

END

I get this as result
NAME_POSTE PathPrinter NamePrinter PathPrinter NamePrinter
-------- ---------------- ------------- ---------------- -------------
Poste1 path 1 name imp 1 path 2 name imp 2
Poste1 path 1 name imp 1 path 2 name imp 2
Poste2 path 1 name imp 1 path 2 name imp 2
Poste2 path 1 name imp 1 path 2 name imp 2
But I want to get this
NAME_POSTE NamePrinter PathPrinter -------------------------------------------------------------------------------
Poste1 name imp 1 path 1
Poste2 name imp 2 path 2

Any help please !!

December 29, 2020 at 6:26 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18778 More actions · Answer 1

First create some consumable test data which you should have provided:

CREATE TABLE #L
(
PK_POSTE int NOT NULL PRIMARY KEY
,NAME_POSTE varchar(20) NOT NULL
);
INSERT INTO #L
VALUES (1, 'Poste1'), (2, 'Poste2');

CREATE TABLE #P
(
FK_POSTE int NOT NULL
,NAME_PARAM varchar(20) NOT NULL
,VALUE_PARAM varchar(20) NOT NULL
,PRIMARY KEY (FK_POSTE, NAME_PARAM)
);
INSERT INTO #P
VALUES (1, 'PathPrinter','path 1')
,(1, 'NamePrinter','name imp 1')
,(2, 'PathPrinter','path 2')
,(2, 'NamePrinter','name imp 2');

Keep the code set based as this is what SQL is good at:

SELECT NAME_POSTE, NamePrinter, PathPrinter
FROM
(
SELECT L.NAME_POSTE, P.NAME_PARAM, P.VALUE_PARAM
FROM #P P
JOIN #L L
ON P.FK_POSTE = L.PK_POSTE
) S
PIVOT
(
MAX(VALUE_PARAM)
FOR NAME_PARAM IN (NamePrinter, PathPrinter)
) P;

Steve Collins SSCrazy Points: 2083 More actions · Answer 2

Using conditional aggregation

select NAME_POSTE, 
       max(iif(NAME_PARAM='NamePrinter', VALUE_PARAM, null)) NamePrinter,
       max(iif(NAME_PARAM='PathPrinter', VALUE_PARAM, null)) PathPrinter
from #P p
     join #L l ON p.FK_POSTE = l.PK_POSTE
group by NAME_POSTE;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

silbahi SSC Enthusiast Points: 190 More actions · Answer 3

Thanks you !

and if I would like to make a call to a stored procedure that allows me to do an insertion or an update on the data of the generated table and behind do the insertions or updates on the other two tables.

are we doing the same concept?

what can you suggest to me?

silbahi SSC Enthusiast Points: 190 More actions · Answer 4

I have a form with different fields that I should to save or to edit the data into two different tables.

The form contains for example:

NAME_POSTE: poste1

PathPrinter: path1

NamePrinter: name imp 1

when adding a new data or when editing an element, I want to save it into two table:

Poste

PK_POSTE ------NAME_POSTE

1 ------------------poste1

2 -----------------poste2

Params_Poste

FK_POSTE ------- NAME_PARAM ---------- VALUE_PARAM

1---------------------PathPrinter------------------path 1

1---------------------NamePrinter----------------name imp 1

2---------------------PathPrinter-----------------path 2

2---------------------NamePrinter----------------name imp 2

any help please ?

Thanks!

Steve Collins SSCrazy Points: 2083 More actions · Answer 5

Please post the CREATE TABLE Data Definition Language (DDL) of both of your tables. Without knowing how the keys (PK_POSTE , FK_POSTE) and constraints are defined we don't have enough information.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Mike01 SSChampion Points: 11393 More actions · Answer 6

You can do merge to #L in the proc and output the insert/deleted values to a temp table, then use the temp table to merge values to #P table

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/