sql query for connecting two tables

Question

Possible Duplicate:
Simple SQL Select from 2 Tables (What is a Join?)

i have a sql query question. Two tables:

Location

lid  State
---  -----
1    MI
2    FL
3    CA

Time

tid lid
a   1
b   1
c   2
d   2
e   3
f   3

Now I want to connect those two tables. If I do:

select l.lid, l.state, t.tid 
from location l, time t 
where l.lid=t.lid

Then it will give me this result:

lid state tid
--- ----- ---
1   MI    a
1   MI    b
2   FL    c
2   FL    d
3   CA    e
3   CA    f

Instead of getting this, I want to have this result:

lid state tid1 tid2
--- ----- ---- ----
1   MI    a    b
2   FL    c    d
3   CA    e    f

How should I write the query?

Also, assume that in the Time table there would be exactly two records for each lid.

Will there only ever be two tids that match? Or can it be any number? — Jodaka, Jul 19 '12 at 14:00
you can do it like this: http://stackoverflow.com/questions/9472986/combining-rows-of-queried-results-by-unique-identifier — paul, Jul 19 '12 at 14:02
The answer to Jodaka's question will determine the answer to your question, JohnnySun. — WWW, Jul 19 '12 at 14:03
You need to stop using implicit joins, they are a very ppoor practice and a SQL Anitpattern. They were replaced with explicit joins 20 years ago, time to stop using this u=outdated and poor rpractice. — HLGEM, Jul 19 '12 at 14:10

score 2 · Answer 1 · answered Jul 19 '12 at 14:53

The problem with what you are trying is that it does not group the results and the GROUP BY clause can be used only in conjunction to an aggregate function.

This can be done easily by using like this

select location.lid as [lid],
        location.state as [state],
        min(time.tid) as [tid1],
        max(time.tid)as [tid2] 

from
      location,time 
where
      location.lid = time.lid 

group by 
      location.state,location.lid;

what the query does is it selects the lid and state as normal but selects the min tid1 and maximum tid2. As there are only two results the one is maximum and the second in minimum.

(The query only selects the maximum and minimum value. If there were three valuse the query will still display only two results.)

and at the end the results are grouped by the location id and the state name.

see it working here SqlFiddle

score 1 · Answer 2 · answered Jul 19 '12 at 14:12

1

SELECT 
    l.lid, l.state, 
    MIN(t.tid) AS tid1,
    MAX(t.tid) AS tid2
FROM
        location AS l
    JOIN 
        time AS t 
            ON l.lid = t.lid
GROUP BY
    l.lid, l.state ;

answered Jul 19 '12 at 14:12

ypercubeᵀᴹ

113,259
19
174
235

score -1 · Answer 3 · answered Jul 19 '12 at 17:01

-1

i got my own version of the solution to this problem.

just connect with two time table and use greater than operator.

select * from
location l, time t1, time t2
where l.lid=t1.lid and l.lid=t2.lid and t1.tid<t2.tid

answered Jul 19 '12 at 17:01

sbs

4,102
5
40
54

sql query for connecting two tables

3 Answers3