CODEWITHSUNDEEP
c#mysqlsql
crytodactyl
crytodactyl

Reputation: 71

How do I select multiple SQL tables in my DataTable grid in C# to display them in the UI via a query?

private DataTable Getvalue(SqlConnection conn, int contentId)
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = @"
                    SELECT
                          S.[Item_ID]
                        , S.[Item]
                        , S.[Description]
                    FROM dbo.Company_Items S
                    LEFT JOIN dbo.Company_StoreItems D
                            ON D.[Item_ID] = S.[Item_ID]
                            AND D.[Content_ID] = @Content_ID
                    WHERE S.[Valid] = 1 
                    ORDER BY S.[Item]";
                cmd.Parameters.AddWithValue("Content_ID", contentId);
                return Fill(cmd, InitvalueTable);
            }
        }

This data table will populate my UI with tables existing in the Items table. In the same tab, I also want to add other pieces of information from other tables, for example

ItemPrice from table dbo.Company_ItemInfo 

PurchaseDate from table dbo_Company_Purchases

How would I add those column inside the query listed above? Can I have 2 FROMs? I am losing myself in the syntax.

Upvotes: 0

Views: 50

Answers (1)

henoc salinas
henoc salinas

Reputation: 1054

you can use UNION for multiple selects.

SELECT a.name as name, a.type as type, a.location as location, '' as price from TableA a
UNION
SELECT '' as name, b.type, b.location, b.price from TableB b
ORDER BY price;

for you code, example:

SELECT S.[Item_ID] , S.[Item] , S.[Description] 
    FROM dbo.Company_Items S 
        LEFT JOIN dbo.Company_StoreItems D ON D.[Item_ID] = S.[Item_ID] AND D.[Content_ID] = @Content_ID 
    WHERE S.[Valid] = 1 
    
UNION 

SELECT S.[Item_ID] , S.[Item] , S.[Description] 
    FROM dbo.Company_Items S 
        LEFT JOIN dbo.Company_StoreItems D ON D.[Item_ID] = S.[Item_ID] AND D.[Content_ID] = @Content_ID 
    WHERE S.[Valid] = 1 
    
ORDER BY Item

Upvotes: 1

Related Questions