SQL code and datagrid
-
Hi, im not so good with SQL so pls bear with me if this is too easy... :) anyways i'd like to display information from two tables "Diagnosis" and "DiagPhar" in a datagrid...my question is how do i code the SQL command text for this...i tried using ways i got from the net but its not working. also how do i setup the datagrid to display the information from two tables. CODER
-
Hi, im not so good with SQL so pls bear with me if this is too easy... :) anyways i'd like to display information from two tables "Diagnosis" and "DiagPhar" in a datagrid...my question is how do i code the SQL command text for this...i tried using ways i got from the net but its not working. also how do i setup the datagrid to display the information from two tables. CODER
-
Hi Try : Select * from Diagnosis,Diagnosis this brings the two table in one result set and you can show the result set in one grid
-
Hi, im not so good with SQL so pls bear with me if this is too easy... :) anyways i'd like to display information from two tables "Diagnosis" and "DiagPhar" in a datagrid...my question is how do i code the SQL command text for this...i tried using ways i got from the net but its not working. also how do i setup the datagrid to display the information from two tables. CODER
Sounds like you need to JOIN your tables together. If you want only rows that appear in both tables, use an INNER JOIN. If you want all rows that appear on the left-hand side of the JOIN, with any data appearing on the right-hand side if available, use a LEFT JOIN. Vice-versa, use a RIGHT JOIN. If you want all rows from both tables, regardless of whether corresponding data appears on the other side, use a FULL OUTER JOIN. Finally, if you don't want to use a join condition (the ON clause) and want to output every row of your left-hand table joined to every row of your right-hand table, use a CROSS JOIN. Example: you have tables OrderLine and Product. An OrderLine maps between an order and the products on that order, and includes the quantity ordered. You want to print an invoice, listing the product details and total prices. You might write something like:
SELECT
OrderLine.Sequence,
OrderLine.Quantity,
Product.ShortDesc,
Product.Price AS ItemPrice,
OrderLine.Quantity * Product.Price AS LinePrice
FROM
OrderLine INNER JOIN Product
ON OrderLine.ProductID = Product.ProductIDIf, somehow, we had OrderLine rows with ProductID not in the Product table, and we wanted to show those anyway, we would use a LEFT JOIN in the above query. Any fields we refer to from Product, for OrderLine rows with no matching ProductID, will be NULL (e.g. ShortDesc and Price will be NULL for the above query). The INNER JOIN omits these rows. Note that if there is more than one row on each side that matches the join condition, all those rows will be output, joined to each other. For the sake of argument, let's say that we have two OrderLine rows which refer to ProductID 10, and there are two rows in Product where ProductID is 10 (you would try to avoid this in an ordering system!). Let's give them descriptions ProductA and ProductB. You might get the result
Sequence Quantity ShortDesc ItemPrice LinePrice
1 2 ProductA 5 10 1 2 ProductB 6 12 2 1 ProductA 5 5 2 1 ProductB 6 6Stability. What an interesting concept. -- Chris Maunder
-
Hi MasudM thx for the hint. anyways how can i display the data using ONE datagrid?..i would appreciate it if u showed me how... thx again! CODER
Hi As Mike Said below a full description if need join ! I do not exactly know what are you after if these two tables have relation you probably need kind of join ! but I can give another hint if you don't know it before ! when you use SqlDataAdapter (or Command) it only turns SqlData to some format that visul Studio can show . then you fill the DataSet SqlDataAdapter.Fill(DataSet,"TableName"); it brings the sql Data to a format Like Sql But Viewable with visual Sudio Tools ! so you can add two SqlTable to one Table in DataSet then can show that table with a grid . But Mike's message is very good for joinning Two tables.it works at the SqlServer Level .
-
Sounds like you need to JOIN your tables together. If you want only rows that appear in both tables, use an INNER JOIN. If you want all rows that appear on the left-hand side of the JOIN, with any data appearing on the right-hand side if available, use a LEFT JOIN. Vice-versa, use a RIGHT JOIN. If you want all rows from both tables, regardless of whether corresponding data appears on the other side, use a FULL OUTER JOIN. Finally, if you don't want to use a join condition (the ON clause) and want to output every row of your left-hand table joined to every row of your right-hand table, use a CROSS JOIN. Example: you have tables OrderLine and Product. An OrderLine maps between an order and the products on that order, and includes the quantity ordered. You want to print an invoice, listing the product details and total prices. You might write something like:
SELECT
OrderLine.Sequence,
OrderLine.Quantity,
Product.ShortDesc,
Product.Price AS ItemPrice,
OrderLine.Quantity * Product.Price AS LinePrice
FROM
OrderLine INNER JOIN Product
ON OrderLine.ProductID = Product.ProductIDIf, somehow, we had OrderLine rows with ProductID not in the Product table, and we wanted to show those anyway, we would use a LEFT JOIN in the above query. Any fields we refer to from Product, for OrderLine rows with no matching ProductID, will be NULL (e.g. ShortDesc and Price will be NULL for the above query). The INNER JOIN omits these rows. Note that if there is more than one row on each side that matches the join condition, all those rows will be output, joined to each other. For the sake of argument, let's say that we have two OrderLine rows which refer to ProductID 10, and there are two rows in Product where ProductID is 10 (you would try to avoid this in an ordering system!). Let's give them descriptions ProductA and ProductB. You might get the result
Sequence Quantity ShortDesc ItemPrice LinePrice
1 2 ProductA 5 10 1 2 ProductB 6 12 2 1 ProductA 5 5 2 1 ProductB 6 6Stability. What an interesting concept. -- Chris Maunder