Combining the results of two tables in one query

I have a query

select* from MedicationCombination mc
where mc.MedicationId = 2

And the result of the query is:

Name        Dosage
-----       ------
Med1        12
Med2        14
Med3        16

I want to put this query into a Stored Procedure which will take MedicationId as parameter and return result as:

Name                 Dosage
----                 ------
Med1/Med2/Med3      12/14/16

I want to use this SP in another query which is:

select Id, Category 
from Medication

Result is:

Id      Category
--      --------
1       Psychotropic

I want to use SP in the above query such that the result would be like:

Id  Category         Name                Dosage
--  --------         ----                -----开发者_C百科-
1   Psychotropic    Med1/Med2/Med3      12/14/16

What could be the possible solutions?

It is not the job of your DBMS to format your data the way you'd like them to look, but the application langage's. Therefore, query your database with the first query you posted and use whatever language you're using to format it the way you want.

If you want to make a JOIN with another table, you have to specify conditions. If you'd just like to make a cartesian product, you can use a CROSS JOIN :

SELECT Id, Category, Name, Dosage
FROM MedicationCombination
CROSS JOIN Medication
WHERE MedicationId = 2

First answer:

SELECT DISTINCT @YourMedicationID AS CategoryId
        ( SELECT    ( Name + '/' )
          FROM      MedicationCombination p
          WHERE     p.MedicationId = p2.MedicationId
          ORDER BY  Name
        FOR
          XML PATH('')
        ) AS NAME ,
        ( SELECT    ( CAST(Dosage AS VARCHAR(10)) + '/' )
          FROM      MedicationCombination p
          WHERE     p.MedicationId = p2.MedicationId
          ORDER BY  Name
        FOR
          XML PATH('')
        ) AS Dosage
FROM    MedicationCombination p2
WHERE   p2.MedicationId = @YourMedicationID
GROUP BY NAME ,
        MedicationId

Produces this:

CategoryId     NAME              Dosage

2              Med1/Med2/Med3/   12/14/16/

Joining to your Medication Table should be trivial now.

select * from Medication m 
join (result from previous query) t on t.CategoryId=m.CategoryID