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

About SQL!

Table1?F
ID    starttime  overtime
A01   2001-9-4  2001-9-6
B01   2001-9-3  2001-9-5

Table2?F
ID    state  Num
A01    A     5
A01    B     3
B01    A     4
B01    B     2

Query result ?F

Time      state  Num
2001-9-3   A     4
2001-9-3   B     2
2001-9-4   A     9
2001-9-4   B     5
2001-9-5   A     9
2001-9-5   B     5
2001-9-6   A     5
2001-9-6   B     3

How to write this 'SQL' sentence ?
0
lingxin
Asked:
lingxin
2 Solutions
 
CrazyOneCommented:
SQL.Add('SELECT Table1?F.ID, Table1?F.starttime, Table1?F.overtime, Table2?F.State, Table2?F.Num');
SQL.Add('FROM Table2?F INNER JOIN Table1?F ON Table2?F.Id = ''Table1?F.Id''');
SQL.Add('ORDER BY Table1?F.starttime);


The Crazy One
0
 
CrazyOneCommented:
Oops sorry I see you don't want the ID or the overtime. And made a few other mistakes so disregard my first comment.

with Query1 do begin
  Close;
     SQL.Clear;
     SQL.Add('SELECT Table1?F.starttime, Table2?F.State, Table2?F.Num');
     SQL.Add('FROM Table1?F INNER JOIN Table2?F ON Table1?F.Id = Table2?F.Id');
     SQL.Add('ORDER BY Table1?F.starttime);
     If not Prepared then Prepare;
     Open;
     First;
end;
0
 
lucikaCommented:
If the tables to set so:

Table1?F
ID    starttime  overtime
A01   2001-9-4  2001-9-7
B01   2001-9-3  2001-9-6

Table2?F
ID    state  Num
A01    A     5
A01    B     3
B01    A     4
B01    B     2

What result of query will be exact?
1) or 2)?

1)
Time      state  Num
2001-9-3   A     4
2001-9-3   B     2
2001-9-4   A     9
2001-9-4   B     5
2001-9-5   A     9
2001-9-5   B     5
2001-9-6   A     9
2001-9-6   B     5
2001-9-7   A     5
2001-9-7   B     3

2)
Time      state  Num
2001-9-3   A     4
2001-9-3   B     2
2001-9-4   A     9
2001-9-4   B     5
2001-9-6   A     9
2001-9-6   B     5
2001-9-7   A     5
2001-9-7   B     3

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
lucikaCommented:
SELECT T3.starttime, Table2?F.State, sum(Table2?F.Num)
FROM Table1?F t3, Table1?F INNER JOIN Table2?F ON Table1?F.Id = Table2?F.Id
where t3.starttime between Table1?F.starttime and Table1?F.overtime
group by t3.starttime, Table2?F.state

union

SELECT T3.overtime, Table2?F.State, sum(Table2?F.Num)
FROM Table1?F t3, Table1?F INNER JOIN Table2?F ON Table1?F.Id = Table2?F.Id
where t3.overtime between Table1?F.starttime and Table1?F.overtime
group by t3.overtime, Table2?F.state;
0
 
lucikaCommented:
It would be desirable to hear your commentings.
My answer obeys problems?
0
 
JaymolCommented:
What's this got to do with Delphi?
0
 
JaccoCommented:
What database do you want to use? And a answer to lucika first comment is required...

Regards Jacco

0
 
PhilatHomeCommented:
Table1?F
ID    starttime  overtime
A01   2001-9-4  2001-9-7
B01   2001-9-3  2001-9-6

Table2?F
ID    state  Num
A01    A     5
A01    B     3
B01    A     4
B01    B     2

SQL will not produce a result from every record in table1, using INNER JOIN, if there is not an entry in Table2 for every possible ID.
So LEFT JOIN may be better

SELECT a.overtime,b.state,b.Num
FROM Table1 a
LEFT JOIN Table2 on a.ID = b.ID

Cheers
0
 
geobulCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

split points between CrazyOne and lucika

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Thanks,

geobul
EE Cleanup Volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now