|
It sounds like you have a scenario where you want to save data across multiple tables in a database using a single form. Additionally, you want to create a master table to store foreign keys and display the data in the frontend. Let's break down your requirements:
1. Generating a Common ID for Multiple Tables:
When you want to add data to multiple tables at once and have them share a common identifier, you typically use a primary key (ID) that is common across all related tables. This can be achieved through database design and relationships.
For example, let's say you have four tables: TableA, TableB, TableC, and TableD. Each of these tables has its own data, but they all share a common identifier, which could be a foreign key linking to a MasterTable.
Here's a simplified example:
CREATE TABLE MasterTable (
MasterID INT PRIMARY KEY,
-- Other columns as needed
);
CREATE TABLE TableA (
ID INT PRIMARY KEY,
MasterID INT,
-- Other columns for TableA
FOREIGN KEY (MasterID) REFERENCES MasterTable(MasterID)
);
-- Repeat the same structure for TableB, TableC, and TableD
When you insert data into MasterTable, you generate a unique MasterID and use it as a foreign key in the other tables. This way, you can maintain relationships between the tables.
2. Displaying Table Rows as Columns in the Frontend:
If you want to display data from a table row as columns in the frontend, you'll need to use SQL queries or your backend programming language to transform the data before sending it to the frontend.
For example, suppose you have a table named Data:
CREATE TABLE Data (
ID INT PRIMARY KEY,
MasterID INT,
ColumnName VARCHAR(50),
ColumnValue VARCHAR(50),
FOREIGN KEY (MasterID) REFERENCES MasterTable(MasterID)
);
This table stores data in a key-value pair format, where each row represents a piece of data related to a MasterID. To display this data with columns dynamically created based on the ColumnName values, you can use a pivot query.
Here's a simplified example in SQL:
SELECT
MasterID,
MAX(CASE WHEN ColumnName = 'Column1' THEN ColumnValue END) AS Column1,
MAX(CASE WHEN ColumnName = 'Column2' THEN ColumnValue END) AS Column2,
-- Add more columns as needed
FROM Data
GROUP BY MasterID;
This query transforms rows into columns based on the unique values in the ColumnName column.
Keep in mind that the specifics of these solutions might depend on the exact requirements of your application, the database system you're using, and the programming language/framework of your frontend.
|
|
|
|
|
When we have multiple users, What's the best way connect(logging, query on tables,...) to database?
Which is better and why?
1) Create users(DB User) in the SQL Server for each user(Windows App User).
2) Create a table which is fill with all App user and just connect to SQL Server with a user and query on that table.
|
|
|
|
|
The only possible answer to that question is "it depends". There is no single "best" option; that's why there are different options available. Each option has both benefits and drawbacks, depending on your specific requirements.
For example, if you use SQL Authentication, then you have to store the SQL credentials somewhere on each client machine that needs to access the database. That runs the risk that a technically-minded user could find the credentials and connect directly to your database, bypassing the restrictions implemented in your code. You would have to deliberately design your database to restrict what the SQL user could do in order to mitigate this.
On the other hand, if you're using a web application / api to access your database, then that will typically run as a highly restricted local user on the web server. Setting that up to use Windows authentication is more effort than using SQL authentication. And since all requests would effectively be running as the same local user, you wouldn't be able to use the authenticated user to restrict access to the data.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
There is no such thing as "the best option". There is only what is the most appropriate given the requirements for the application, the application type, and the environment it's running in, which we know nothing about.
There are far more options than just the two you listed.
|
|
|
|
|
I am going to guess you have an application. Users (plural) use the application not the database.
The application, not the users, use the database. So there is only one user which is only visible to the application. The reason for this is because attempting to manage users both at the database and application level rapidly becomes a problem and provides no benefit.
The application itself, should provide a mechanism to validate each user. Then you can do things like log actions, in the application, for each user in the database (a table for that.) The application will use the database to implement this but it does not have anything to do with database users.
|
|
|
|
|
I can't figure out the following:
I have order header and detail tables with UPC codes and the order numbers they belong to. There can be many different UPC codes for each order. The Order IDs are in the header table and the UPC codes are in the detail table.
I must find UPC codes that have been used for more than one order, in other words, where the same UPC code exists for two or more distinct orders.
I have tried:
SELECT OD.UPC, OH.OrderId, COUNT(DISTINCT OH.OrderId) [COUNT]
FROM OrderDetail OD
LEFT JOIN OrderHeader OH ON OH.OrderHeaderId = OD.OrderHeaderId
GROUP BY OD.UPC, OH.OrderId
HAVING COUNT(DISTINCT OH.OrderId) > 1 But this query returns nothing and I'm not sure if it's because there are no duplicate orders or because the query is wrong.
Any help will be tremendously appreciated. Thank you.
The difficult we do right away...
...the impossible takes slightly longer.
modified 31-Jul-23 11:18am.
|
|
|
|
|
How about:
SELECT UPC, COUNT(DISTINCT OrderHeaderId)
FROM OrderDetail
GROUP BY UPC
HAVING COUNT(DISTINCT OrderHeaderId) > 1
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Brilliant! I guess I was overthinking it.
Thanks, Richard. You saved the last few hairs I have left.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
There are some website that provide Demos for their premium Html/css/javascript themes while we can see and copy the source code without paying anything. Why?
Why don't they only provide a snapshot of the website theme to prevent any illegal copy?
|
|
|
|
|
|
Is there possible to get an UNION in such a way that second part of UNION to be ordered ?
I have:
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2 ORDER BY 3
The select from table1 will always get one row, and I need to order just records that come from table2, which could be more than one row ... it is possible to achieve that by SQL ?
P.S. I am using SQL Server.
modified 29-May-23 13:54pm.
|
|
|
|
|
_Flaviu wrote: it is possible to achieve that by SQL ?
No.
There are however solutions using derived languages such as TSQL or PL/SQL. You would need to specify which database you are using however for any consideration of that.
|
|
|
|
|
SQL Server (from Microsoft)
|
|
|
|
|
The ORDER BY clause applies to the entire results; you can't make it only apply to one part of a UNION.
However, you could add an additional column to indicate which part of the UNION the row belongs to, and add that to your ORDER BY statement:
SELECT column_name(s), 0 As QueryPart FROM table1
UNION ALL
SELECT column_name(s), 1 As QueryPart FROM table2
ORDER BY QueryPart, SomeOtherColumn
If you don't want the additional column to be included in your results, you can use a subquery or CTE to hide it:
WITH cteUnion As
(
SELECT column_names(s), 0 As QueryPart FROM table1
UNION ALL
SELECT column_name(s), 1 As QueryPart FROM table2
)
SELECT
column_name(s)
FROM
cteUnion
ORDER BY
QueryPart,
SomeOtherColumn
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
How about:
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM
(SELECT column_name(s) FROM table2 ORDER BY 3)?
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
I have tried:
SELECT a.id, a.name FROM my_table a WHERE a.id = 10416
UNION
SELECT b.id, b.name FROM
(SELECT b.id, b.name FROM b.my_table b WHERE b.parent_id = 10416)
But it doesn't like it:
SQL
Executing SQL directly; no cursor.
Incorrect syntax near ')'.
Statement(s) could not be prepared.
Did I understand correctly your thought?
|
|
|
|
|
I'm sorry, try this slight modification:
SELECT a.id, a.name FROM my_table a WHERE a.id = 10416
UNION
SELECT S.id, S.name FROM
(SELECT b.id, b.name FROM b.my_table b WHERE b.parent_id = 10416) AS S
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
Yes, that's worked:
SELECT a.id, a.name FROM my_table a WHERE a.id = 10416
UNION
SELECT S.id, S.name FROM
(SELECT b.id, b.name FROM b.my_table b WHERE b.parent_id = 10416) AS S
but soon as I put ORDER BY:
SELECT a.id, a.name FROM my_table a WHERE a.id = 10416
UNION
SELECT S.id, S.name FROM
(SELECT b.id, b.name FROM b.my_table b WHERE b.parent_id = 10416 ORDER BY 2) AS S
Error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
|
|
|
|
|
Whoops! I guess that's why nobody else recommended that. I forgot that ORDER BY is not allowed in derived tables.
I think the temporary table is probably your best option.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
I know this is not going to be a popular opinion, but ...
1) Create a #TEMP table
2) Insert into the table with part 1 of the query
3) Insert into the table with part 2 (sorted)
Return the dataset from the #TEMP table
|
|
|
|
|
That is probably how I would do it. Although temp tables are also database specific the general idea and the layout of the solution works across different types of databases.
|
|
|
|
|
Is there any guarantee that the temp table will be returned in the insertion order? If not, you'd have to add an identity column and return the temp table sorted on that.
Keep Calm and Carry On
|
|
|
|
|
k5054 wrote: will be returned in the insertion order?
Unlikely.
And would not work anyways. Because you would do first one query then the second.
So of course the temp table would need to provide a way to order it.
|
|
|
|
|
I'm trying to set up a repository project in a WPF solution. I'm following this[^]
First I do
cd MyApp.Repository
Add-Migration InitialCreate -Project MyApp.Repository
So far, so good. The Migrations folder appears in he repository project and everything seems ok.
Then I do
update-database
and get back
No DbContext was found in assembly 'MyApp.Crypto'. Ensure that you're using the correct assembly and that the type is neither abstract nor generic.
The DBContext is in MyAppRepository. MyApp.Crypto is another C# class library in the solution. I have no clue wy EF is lookin in there.
What am I doing wroing????
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
