CODEWITHSUNDEEP
sqlt-sql
sony
sony

Reputation: 21

sql query to join 3 tables using joins

I wanted to display the column values of 3 tables(datalogging,temprature,pressure) as shown below and my query also shown below but here temprature values are repeating,i want all the corresponding values for particular date how can i do it? can anybody help me please?

Tables are

  1. datalogging5

    name     shift name   operator   id   date                     plant   line   machi
-------  -----------  ---------  ---  -----------------------  ------  -----  -----
ashwini  shift1(7-3)  Operator1  102  2011-06-07 00:00:00.000  Plant1  Line1  mc1
pradeep  shift1(7-3)  Operator1  101  2011-06-06 00:00:00.000  Plant1  Line1  mc1
...

  2. temprature

    temprature  time                     date
----------  -----------------------  -------------------
27          1900-01-01 15:45:41.000  2011-06-06 00:00:00
27.3        1900-01-01 15:50:41.000  2011-06-06 00:00:00
...

  3. pressure

    temprature  time                     date
----------  -----------------------  -------------------
0.5         1900-01-01 15:45:41.000  2011-06-06 00:00:00
0.7         1900-01-01 15:50:41.000  2011-06-06 00:00:00
...

my sql query is 

select Date=convert(varchar(12),(t1.date),101), t1.time, operatorname, 
    machine, line, t2.time, Temprature, Pressure
from datalogging5 t 
inner join temprature t1 on t1.date=t.date 
inner join pressure t2 on t.date=t2.date  order by t.date

Upvotes: 2

Views: 9383

Answers (2)

Thomas
Thomas

Reputation: 64674

You need to join the temperature and pressure tables on both date and time (since they are apparently stored in separate columns)

Select D.Date, T.Time...
From DataLoggings As D
    Join Temperature As T
        On T.Date = D.Date
    Join Pressure As P
        On P.Date = T.Date
            And P.Time = T.Time

There are some catches to your schema and this query:

  1. The time in the Temperature table must match exactly, to the millisecond, to the time in the Pressure table.
  2. You must have both a Temperature reading and a Pressure reading for a given time for either to be returned in the list. One way around that problem is to build a schedule of seconds in the day from a Numbers or Tally table which is an ugly solution.

You might consider a measurements table instead of a separate table for Temperature and Pressure. So something like:

Create Table Measurements
    (
    ReadingType varchar(15) not null
    , Reading float not null
    , Date date not null
    , Time time not null
    , Check( ReadingType In('Temperature','Pressure') )
    )

Upvotes: 1

Bryce Siedschlaw
Bryce Siedschlaw

Reputation: 4226

I'm not especially fluent in SQL, so if I'm doing something wrong let me know, but can't you just do something like:


SELECT CONVERT(varchar(12),(t1.date),101) AS Date, t1.time, t.operatorname, 
    t.machine, t.line, t2.time, t1.temprature, t2.pressure
FROM datalogging5 t 
INNER JOIN temprature t1 ON t1.date=t.date 
INNER JOIN pressure t2 ON t.date=t2.date
WHERE t.date = '2011-6-10'
ORDER BY t.date;

You could replace the 2011-6-10 with whatever your search query is. As for your temprature repetition problem, I think the problem is that you're trying to connect tables via the date columns. There's the possibility that more than one datalogging or temprature entry has the same date and so when you run your query, it'll show up once for each record it matches to making it seem like a duplicate.

Anyway, let me know if that code is what you were looking for or not.

Upvotes: 1

Related Questions