Two records from same table. Results show in one row.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using: MS Query, XP, Oracle 8 - I

I have a table with pick-ups and drop offs in the same table.

There is a code of “0†for the pickup and “1†for the drop off.

I want the result to have one row with data from both records.

How can this be done?

Thanks in advance.
 
Richard said:
Using: MS Query, XP, Oracle 8 - I

I have a table with pick-ups and drop offs in the same table.

There is a code of “0†for the pickup and “1†for the drop off.

I want the result to have one row with data from both records.

How can this be done?

Thanks in advance.
Click to expand...

Hi Richard,

This group is about Microsoft Access, but the concept is the same so
here goes...

Add the same table twice to the FROM clause. For convenience give each
instance a short alias such as "A" and "B". I'm assuming you have some
way of linking the pick up and drop off records, e.g., a common ID. Join
the tables on this ID. Add the field containing "code" to the SELECT
clause, once from each table. The SQL result looks something like this:

SELECT
A.Code
, B.Code
FROM
MyTable AS A INNER JOIN MyTable AS B
ON A.ID = B.ID
;

or, in the Oracle dialect

SELECT
A.Code
, B.Code
FROM
MyTable AS A
, MyTable AS B
WHERE
A.ID = B.ID
;

Either should work.

HTH
 
Back
Top