Selecting one record from one table and multiple from second table
-
My company has a project database that has numerous columns. We track everything between tables with the project number, but because of how screwed up in the past the database got there is another column which is actually the primary keys called OrderStatusID. So if I want to navigate forward through the records I use the following SQL statement:
SELECT TOP 1 * FROM [Order Status] WHERE OrderStatusID > " & CurrentRecordID & " ORDER BY OrderStatusID"
Now there is another table that holds certificates for the projects. A project may have zero to many certificates. So I was trying to use the SQL statement:
SELECT TOP 1 * FROM [Order Status] LEFT JOIN Lead_Free_Certs ON (CAST([Order Status].[Project #] AS nvarchar(255)) = Lead_Free_Certs.[Project Num]) WHERE (OrderStatusID > " & CurrentRecordID & ") ORDER BY OrderStatusID, Lead_Free_Certs.[Cert Num] DESC
However if a site has more then one certificate, only the first one is returned because of the
SELECT TOP 1statement. How can this statement be modified so that it would return one row form the[Order Status]table and multiple rows from theLead_Free_Certstable? Is this even possible? Please keep in mind that because of the way the database was set up theSELECT TOP 1(or something similar) must remain in place because I finding the next highest OrderStatusID. Thanks in advanced for any help. -
My company has a project database that has numerous columns. We track everything between tables with the project number, but because of how screwed up in the past the database got there is another column which is actually the primary keys called OrderStatusID. So if I want to navigate forward through the records I use the following SQL statement:
SELECT TOP 1 * FROM [Order Status] WHERE OrderStatusID > " & CurrentRecordID & " ORDER BY OrderStatusID"
Now there is another table that holds certificates for the projects. A project may have zero to many certificates. So I was trying to use the SQL statement:
SELECT TOP 1 * FROM [Order Status] LEFT JOIN Lead_Free_Certs ON (CAST([Order Status].[Project #] AS nvarchar(255)) = Lead_Free_Certs.[Project Num]) WHERE (OrderStatusID > " & CurrentRecordID & ") ORDER BY OrderStatusID, Lead_Free_Certs.[Cert Num] DESC
However if a site has more then one certificate, only the first one is returned because of the
SELECT TOP 1statement. How can this statement be modified so that it would return one row form the[Order Status]table and multiple rows from theLead_Free_Certstable? Is this even possible? Please keep in mind that because of the way the database was set up theSELECT TOP 1(or something similar) must remain in place because I finding the next highest OrderStatusID. Thanks in advanced for any help. -
How about
select *
from lead_free_certs
join (
SELECT TOP 1 *
FROM [Order Status]
WHERE OrderStatusID > " & CurrentRecordID & "
ORDER BY OrderStatusID
) t on (CAST(t.[Project #] AS nvarchar(255)) = Lead_Free_Certs.[Project Num])When a record is being loaded from the order status table, if there isn't a matching project number in the lead_free_certs table, then no rows are returned. Would an Right Join work in this circumstance?
-
How about
select *
from lead_free_certs
join (
SELECT TOP 1 *
FROM [Order Status]
WHERE OrderStatusID > " & CurrentRecordID & "
ORDER BY OrderStatusID
) t on (CAST(t.[Project #] AS nvarchar(255)) = Lead_Free_Certs.[Project Num])By using an right join it worked, the only problem was that it would have to change a lot of code because all the column indexes are different. I tried reversing the two section of the SQL Statement and came up with:
SELECT TOP 1 * FROM [Order Status] WHERE OrderStatusID > " & CurrentRecordID & " ORDER BY OrderStatusID JOIN (SELECT * FROM Lead_Free_Certs) t ON t.[Project Num] = CAST([Order Status].[Project #] AS nvarchar(255))
However I'm getting an error about an error near the JOIN but I don't know what it is.