SQL Server Get this result table without creating a new table to query?

qacovj5a 于 2023-11-16 发布在其他

关注(0)|答案(1)|浏览(114)

I wanted to see if there was a way to write these two queries as one and avoid creating a new table. I need to list the SerialNums , ModelNums of the Vacuum relation along with the Designer's EmployeeID, Designer's FirstName , Designer's LastName , the Technician's EmployeeID , Technician's FirstName , and Technician's LastName . I have a relation name Employee that contains the personal details of employees, a relation for both Designer and Technician , junction relations Design and Inspection , and I have a relation named Vacuum . For reference, here is the result set:

Here are my current queries that I would like to make into one and avoid creating a table with:

SELECT DISTINCT SerialNum, ModelNum_FK AS ModelNum, Design.EmployeeID_FK AS DesignerEmpID, FirstName AS DesignerFirstName, LastName AS DesignerLastName 
INTO VacuumDesigner
FROM Vacuum
FULL OUTER JOIN Design
ON SerialNum = Design.SerialNum_FK
FULL OUTER JOIN Employee
ON EmployeeID = Design.EmployeeID_FK
WHERE SerialNum IS NOT NULL;

SELECT SerialNum, ModelNum, DesignerEmpID, DesignerFirstName, DesignerLastName, EmployeeID AS TechEmpID, FirstName AS TechFirstName, LastName AS TechLastName FROM VacuumDesigner
FULL OUTER JOIN Inspection
ON SerialNum = SerialNum_FK
FULL OUTER JOIN Employee
ON EmployeeID = Inspection.EmployeeID_FK
WHERE SerialNum IS NOT NULL;

DROP TABLE VacuumDesigner;

Any and all SQL tips on formatting this/these queries are much appreciated, thanks!

sql-server

来源：https://stackoverflow.com/questions/77429738/get-this-result-table-without-creating-a-new-table-to-query

1条答案

按热度按时间

nr9pn0ug1#

Here is an alternative approach, but note I am NOT using "select distinct" deliberately. "Select distinct" adds to query effort and can be a huge waste of time, so before just adding it - inspect the raw results to see if there really is a need for it.

Also note that every "full outer join" is also adding to query effort, and this join type is often trying to compensate for missing data and/or poor data quality. As your tables contain "foreign keys" this would suggest that your data should have good/reasonable data quality - so unless you absolutely know you need them don't use "full outer joins". Instead start with "left join" which will allow data from vacuums to be listed even if there is no designer or no inspector. If there is no designer then the designer columns will be nulls, and similarly if there is no inspector those columns will be nulls.

SELECT
      v.SerialNum
    , v.ModelNum_FK AS ModelNum
    , Design.EmployeeID_FK AS DesignerEmpID
    , FirstName AS DesignerFirstName
    , LastName AS DesignerLastName
    , ie.EmployeeID AS TechEmpID
    , ie.FirstName AS TechFirstName
    , ie.LastName AS TechLastName
FROM Vacuum v
LEFT JOIN Design d ON v.SerialNum = d.SerialNum_FK
LEFT JOIN Employee e ON d.EmployeeID_FK = e.EmployeeID
LEFT JOIN Inspection i ON v.SerialNum = i.SerialNum_FK
LEFT JOIN Employee ie ON i.EmployeeID_FK = ie.EmployeeID
WHERE v.SerialNum IS NOT NULL

Now, if every vacuum has at least 1 designer you could use "inner joins" to designer and (first instance of) employee. Similarly, if every vacuum has at least 1 inspector then you could use "inner joins" for inspection table and (second instance of) employee.

nb If there is more than 1 designer and/or more then 1 inspector for a serial then you can expect the query to produce multiple rows for each serial number. "Select distinct" will NOT reduce such a result back to 1 row per serial because "select distinct" considers the whole row together, it will only discard rows where the entire row is exactly the same as some other row.

e.g. these are NOT removed by "select distinct" because at least 1 part of each row is different:

123, designer x, inspector y
123, designer x, inspector z

Regarding "formatting" of your query this is either going to be established by convention at your site - or - by personal preferences. As you see above I prefer a "comma first" approach to lists. You can use PoorSQL as a way to experiment with formatting options. In my example I "expand comma lists" and also turn off "trailing commas". It is more common for folks to use "trailing commas" but I truly disagree with that convention.

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server Get this result table without creating a new table to query?

1条答案

相关问题

热门标签

最新问答