Need a query or transaction to export data into a set format

Im trying to export data from a database into a set format so that it can be interpreted by myob correctly. I have the exporting function working great and I have half worked out the sql query but am having trouble with how to put it all together.

The following is a simple example to illustrate what I'm trying to achieve

$sql = "SELECT (CONCAT(PICKUPID,DROPOFFID) AS ITEM), 
               (CONCAT(Rego, Pickup, Dropoff, booker, date) AS Description) 
        FROM booking, myob";

The issue is that I need to pull the pickup and dropoff /ID out of the myob table in reference to what is in the booking table.

booking Table Columns

  • Rego
  • Pickup
  • Dropoff
  • Date
  • booker

myob Table Columns

  • MYOBID (where MYOBID = PICKUPID OR DROPOFFID)
  • Address (where Address = Dropoff or Pickup)

I am hoping to do this in an SQL query or a PL/SQL transaction but I'm having trouble getting my head around it. Any help would be appreciated (hope the questions not to confusing)

*Data in Tables***

booking

Rego , Pickup , Dropoff , Date    , booker

123,    bris,     sydn,      1/2/12,    barry

myob

MYOBID , Address

Q,        bris

N,        sydn

OUTPUT

ITEM   , Description

QN,       123 bris sydn 1/2/12 barry

Answers


You need to JOIN the two tables. You may also find some more practical information here. Please come back to us if you are still stuck after reading these.

Also, I see little advantage in CONCAT'ing your fields at query time, I think you'd better leave the formatting out the output to your application layer. Of course, this is probably an overkill if all you are trying to achieve is a quick-and-dirty one-time export.


Need Your Help

Sorting contents in file

python sorting

My file contains about 1602 elements like

What is the difference between name() and local-name()?

xml xpath

I don't really understand the difference between the XPath functions name and local-name.