March 2, 2012 at 9:26 am
Hi,
I'm trying to build a query to gather data from 2 tables where there is no links between them.
First table:
CREATE TABLE [dbo].[TB_InOut](
[ioID] [int] IDENTITY(1,1) NOT NULL,
[idTruck] [int] NOT NULL,
[ioDateHeureEntree] [datetime] NULL,
[ioDateHeureSortie] [datetime] NULL,
[ioAntenna] [int] NULL
second table:
CREATE TABLE [dbo].[TB_outLineUp](
[outID] [int] IDENTITY(1,1) NOT NULL,
[outDateHeure] [smalldatetime] NOT NULL,
[outNoTruck] [varchar](50) NULL,
[driNo] [int] NULL,
[driNom] [varchar](50) NULL,
to make things short, a user enter a truck line up in an interface by giving the truck number, an approximative time when the truck should leave the yard and specify a driver (filling TB_outLineUp). When the truck passed the yard gates, a RFID system pick it up and the table TB_InOut is filled.
Only link between the tables in the date/time (outDateHeure). Truck should normally leave yard in more or less 60 minutes of the programmed time.
Query so far:
SELECT distinct TruckNoUnite,
traNom,
catDesc,
ioDateHeureEntree,
ioDateHeureSortie,
driNom
FROM TB_inOUt
INNER JOIN TB_idtruck ON TB_idtruck.idtruck = TB_INOUT.idtruck
inner join TB_traTransporteurs on TB_idtruck.traID = TB_traTransporteurs.traid
inner join TB_catTruckCategorie on TB_idtruck.truckCategorie = TB_catTruckCategorie.catid
INNER join TB_outLineUp on TB_idTruck.truckNoUnite = TB_outLineUp.outNoUnite
WHERE dbo.TB_InOut.ioDateHeureSortie BETWEEN DATEADD(mi, -60, dbo.TB_outLineUp.outDateHeure) AND DATEADD(mi, 60, dbo.TB_outLineUp.outDateHeure)
Problem is, if the truck left the yard later or sooner, it won't shows up in the query. What I'd like is to have all the lines from table TB_InOut and if there is no corresponding time found in table TB_outLineUp, have driNom as NULL. I tried a Left Join on TB_outLineUp but the query timed out.
How can I do that?
thanks a lot for your precious time and help
March 2, 2012 at 10:08 am
Could you please post the query with LEFT JOIN you've tried and also add some test data into setup...
March 2, 2012 at 10:09 am
March 2, 2012 at 11:30 am
Eugene Elutin (3/2/2012)
Could you please post the query with LEFT JOIN you've tried and also add some test data into setup...
query with LEFT JOIN is almost identical:
SELECT distinct TruckNoUnite,
traNom,
catDesc,
ioDateHeureEntree,
ioDateHeureSortie,
driNom
FROM TB_inOUt
INNER JOIN TB_idtruck ON TB_idtruck.idtruck = TB_INOUT.idtruck
inner join TB_traTransporteurs on TB_idtruck.traID = TB_traTransporteurs.traid
inner join TB_catTruckCategorie on TB_idtruck.truckCategorie = TB_catTruckCategorie.catid
LEFT JOIN TB_outLineUp on TB_idTruck.truckNoUnite = TB_outLineUp.outNoUnite
WHERE dbo.TB_InOut.ioDateHeureSortie BETWEEN DATEADD(mi, -60, dbo.TB_outLineUp.outDateHeure) AND DATEADD(mi, 60, dbo.TB_outLineUp.outDateHeure)
There is 19050 rows in TB_InOut and 13161 un TB_outLineUp. Query with the left join timed out. Query with INNER JOIN takes 3 seconds and gives 9705 rows. I'd like to have 19050 rows:
1003011TanguayCamion 26 pieds2011-10-15 15:06:37.7832011-10-17 08:29:55.810Bergeron Dominique
1003011TanguayCamion 26 pieds2011-10-19 08:10:21.1972011-10-19 08:10:21.197GERMAIN LUC
1003011TanguayCamion 26 pieds2011-11-01 17:31:26.0732011-11-02 08:14:49.310NULL ->IF NOT FOUND WITHIN THE WHERE CLAUSE
1003011TanguayCamion 26 pieds2011-11-02 16:40:09.0732011-11-03 08:08:50.140DAGUZAN VALÉRY
thanks
March 2, 2012 at 1:18 pm
Think of your query this way:
You specify "left outer join" instead of "inner join" to tell sql server to return all rows from TB_inOUt, even if there is no matching row in TB_outLineUp. Next you specify in the where clause that for every row it finds from TB_inOUt it needs to check that the value of TB_InOut.ioDateHeureSortie must be between the values calculated from TB_outLineUp.outDateHeure. Realise what you did? You asked it not only to remove from the results all rows that have a date outside the range, you've also asked it to exclude all rows for which it can't find a value for TB_outLineUp.outDateHeure: TB_InOut.ioDateHeureSortie can never be within a date range for which it can not find a value, hence the rows for which no match is found are removed from the set too.
The problem is that you have applied the filtering in the where clause. You should instead put the check for the time range in the left join's on-clause.
Note: get rid of the distinct in your query. In fact, you can most likely remove it from pretty most all of your queries. If the query returns duplicates without the distinct you've probably made an error in the query; adding distinct only 'hides' the problem, it doesn't magically 'fix' your query and as a result it'll be slow under certain conditions.
Note on the performance: Can you not change TB_outLineUp to contain the idtruck instead of the outNoUnite? For example, do -when the driver name is entered- a lookup on the TB_idtruck table using the truckNoUnite value. But instead of storing the truckNoUnite into the TB_outLineUp, store the idtruck value found for that number. Now when you do the left join between TB_inOUt and TB_outLineUp you don't need to go through TB_idtruck. Instead you can directly join the both tables together on the idtruck value.
March 5, 2012 at 10:55 am
thanks for your help
I'll certainly look into my query more closely. Thanks for your tips, I really appreciate.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply