• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 800
  • Last Modified:

MySQL 5: Inner join problem

Hi

Need some help explaining how I should build my inner join sql  qery

I already have a query, strSqlQuery, but I don't understand why it looks like it does.
(I have no createed this query from the beginning)
why is this question buld in this way, why are there  DATE before the WHERE statment and not after and so on.

This is what i want to do:
1: I get all records from T1 that has the FileDate = yyyyMMdd
2: I need to comapre all these records with T2 table.
3: Uniqe key is  DATE,  T_NR, and K_NR  in both tables
4: I only need the records that the VAULE is not the same


       strSqlQuery = "SELECT " & _
                            "T1.DATE, T1.TIME, T1.VALUE, " & _
                            "T"2.VALUE" & _
                            "FROM T2 INNER JOIN T1 ON " & _
                            "(T1.FileDate '" & fileDate & "') AND " & _
                            "(T1.DATE = T2 .DATE) AND " & _
                            "(T1.T_NR = T2 .T_NR) WHERE " & _
                            "(T1.K_NR = T2 .K_NR) AND " & _
                            "(T1.VALUE<>T1.VALUE)"

0
AWestEng
Asked:
AWestEng
  • 8
  • 4
  • 2
3 Solutions
 
JimBrandleyCommented:
In general, you can think of the FROM clause as building a set of candidate rows, and the WHERE clause as determining the subset of those rows to return. If you write it like this, maybe it's easier to see that.
SELECT t1.date, t1.time, t1.value, t2.value
  FROM t2 INNER JOIN t1
      ON t1.date = t2.date
    AND t1.t_nr = t2.t_nr
    AND t1.k_nr = t2.k_nr
 WHERE t1.filedate = '20070930'
     AND t1.value <> t2.value

However, moving the date up into the FROM clause, as was done here, causes the initial set to be much smaller, so your select should run faster.

Does that help?

Jim

0
 
Jai STech ArchCommented:
you are joining two tables...and use a INNER JOIN for that which mean it is displaying the matching rows of the two tables t2 AND t1

the where clause is generally given after the INNER JOIN which is the syntax of it...so it has been followed...
0
 
AWestEngAuthor Commented:
oki..  how about this, but which is fastest, how should i make the query as fast as possible

SELECT "T1.DATE, T1.TIME, T1.VALUE, T"2.VALUE" & _
                            "FROM T2 INNER JOIN T1 ON " & _
                            "(T1.FileDate '" & fileDate & "') AND " & _
                            "(T1.DATE = T2 .DATE) AND " & _
                            "(T1.T_NR = T2 .T_NR) AND
                             /T1.K_NR = T2 .K_NR) WHERE " & _
                            "(T1.VALUE<>T1.VALUE)"
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
AWestEngAuthor Commented:
or

SELECT "T1.DATE, T1.TIME, T1.VALUE, T"2.VALUE
                            "FROM T2 INNER JOIN T1 ON
                            "(T1.FileDate '" & fileDate & "')  
                            WHERE
                            "(T1.DATE = T2 .DATE) AND
                            "(T1.T_NR = T2 .T_NR) AND
                             /T1.K_NR = T2 .K_NR) AND
                            "(T1.VALUE<>T1.VALUE)"
0
 
JimBrandleyCommented:
I think the first of the two is faster. There might be many with the same filedate, and fewer where the other three columns match as well. The general idea is to let the database engine build the smallest sets to work with you can define as it is building the execution plan.

Jim
0
 
Jai STech ArchCommented:
the first one is how a proper INNER JOIN has to be built..
0
 
AWestEngAuthor Commented:
oki.. but I need to get all records  with  Filedate = xxx-xx-xx from T1 and then see if those  exist in the Table T2

So if I get all Fildate = xxxx-xx-xx from T1 I get about 4000 records those records has
DATE,  T_NR, K_NR and VALUE

I need sto findd these recodsd in table T2 but i only whant to return the records that not has the same VALUE

T1:
FileDate                         DATE               T_NR  K_NR  VALUE
2007-01-01                  2007-01-23     123      C56    455.56
2007-01-01                  2007-01-13       ---         ---       34.45
2007-01-01                   2007-01-18     ---         ---       23.12
2007-01-01                   2007-01-11    ---         ---       54.23

T2:
 DATE               T_NR  K_NR  VALUE
2007-01-23     123      C56    155.56
2007-01-13      ---         ---       34.45
2007-01-18      ---         ---       23.12
2007-01-11  ---         ---       54.23

The records I want is

2007-01-23     123      C56    455.56 From T1
From T2 155.56


0
 
AWestEngAuthor Commented:
The FileDate has one for every monh and are about 3000-5000

So i got '
FileDate T1
2007-01-01  : 3000-5000 records
2007-02-01  : 3000-5000 records
2007-03-01  : 3000-5000 records
2007-04-01  : 3000-5000 records
2007-05-01  : 3000-5000 records
and so on

These recors needs to be tested against the table T2

The most of the records are the samt but some of them has diffrent VALUE in T1 and T2 these are the recorde I want

0
 
JimBrandleyCommented:
I do not think you can improve on your first select. Which columns in the tables have indexes?
0
 
AWestEngAuthor Commented:
and one thing more.. if the record dosent exsist in T2 I need to return that to

is that possible?
0
 
AWestEngAuthor Commented:
FileDate,  DATE, T_NR,  K_NR and   VALUE in T1
DATE, T_NR,  K_NR and   VALUE in T2

so all of them i'm checking here, there are more  columns int hte table.

So T1 and T2 are alomst the same table the only thing that is diffrent is the FileDate column and there are values in the column VALUE that are not the same or they or the record do not exist..

but i can't understand why the first query is the best why having the DATE after the FileDate

because if the T1 record exist in T2 the only thing that is diffrent in that reocrd could be VALUE

and the uniqe key in both T1 and T2 are the same  it's DATE, T_NR,  and K_NR



0
 
JimBrandleyCommented:
First, the ones that do not exist in t2:
SELECT t1.date, t1.time, t1.value, t2.value
  FROM t1 LEFT OUTER JOIN t2
      ON  t1.filedate = '20070930
    AND t1.date = t2.date
    AND t1.t_nr = t2.t_nr
    AND t1.k_nr = t2.k_nr
 WHERE  t1.value <> t2.value OR t2.value IS NULL

Next, the date: If we do not add filedate to the initial join definition, then:
2007-01-01  : 3000-5000 records
2007-02-01  : 3000-5000 records
2007-03-01  : 3000-5000 records
2007-04-01  : 3000-5000 records
2007-05-01  : 3000-5000 records
are all potentially included in the intermediate result set. So max intermediate result set is 25,000 rows. If the engine looks ahead to the where clause, it might not do that. However, by moving the date up, I think you increase the probability of reducing the size from 25,000 rows to 5,000 rows.

Different database engines will handle different queries in different ways. It also depend on table sizes and distributions on various candidate indexes that may be used in the execution plan.

The only way to be certain you are getting the best performance you can is by altering the select several times, then getting the engine to show you the execution plan for each. Then, the results may change over time as the data changes.

Jim
0
 
AWestEngAuthor Commented:
that query is what i thought was the best and fastest one,,?

SELECT t1.date, t1.time, t1.value, t2.value
  FROM t1 LEFT OUTER JOIN t2
      ON  t1.filedate = '20070930
    AND t1.date = t2.date
    AND t1.t_nr = t2.t_nr
    AND t1.k_nr = t2.k_nr
 WHERE  t1.value <> t2.value OR t2.value IS NULL

Ok I will test the code and come back to you
0
 
AWestEngAuthor Commented:
hmm OR t2.value IS NULL dosent work

In T1 i have the record but in T2 the record dosent exists at all.. should it still return Null
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now