[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

Multiple data from one table returned using one query

I have a DB2 database that I am querying.   I have multiple tables that I am getting data from but I a owner table and an address table.  They are multiple addresses to one owner.  I need both addresses returned in one query result.

Below is what my query looks like now.  I get two records but need only one showing both addresses.

SELECT
   ISDB8.MVTITLESUBTX.SITECHANGEDESCRIPTION, ISDB8.MVTITLESUBTX.SITECHANGEREASONID, ISDB8.MVTITLESUBTX.STRGISRETURNEDSITE,
   ISDB8.SUBTRANSACTION.CREATEDON, ISDB8.SUBTRANSACTION.SUBTRANSNUMBER,
ISDB8.SUBTRANSACTION.OFFICENUMBER,
 ISDB8.OWNER.LASTNAME,  ISDB8.OWNER.FIRSTNAME, ISDB8.OWNER.MIDDLENAME, ISDB8.OWNER.SUFFIX1,
   ISDB8.ADDRESS.ADDRESSLINE1, ISDB8.ADDRESS.CITY, ISDB8.ADDRESS.STATE1,
   ISDB8.ADDRESS.ZIP, ISDB8.VEHICLE.YEAR1, ISDB8.VEHICLE.MAKE,
   ISDB8.VEHICLE.VIN, ISDB8.TRANSACTION1.TRANSNUMBER, ISDB8.SHIPMENT.SHIPMENTNUMBER,
   ISDB8.SHIPMENT.SHIPMENTDATE, ISDB8.SALEINFO.NETPRICE, ISDB8.FEECHARGED.FEEAMOUNT,
   ISDB8.FEESCHEDULE.FEETYPEID, ISDB8.FEESCHEDULE.FEEUNITID, ISDB8.FEESCHEDULE.FEEAMOUNT as SCH_Fee_Amt,
   ISDB8.MTAS.MTASCODE, ISDB8.MTAS.DESCRIPTION as MTAS_DESC, ISDB8.COUNTY.COUNTYNAME,
   ISDB8.OPERATOR.OPERATORNUMBER, ISDB8.VM_SHIPMENTSTATUS.DESCRIPTION,
ISDB8.OFFICE.OFFICENAME
FROM
   ISDB8.MVTITLESUBTX,
   ISDB8.SUBTRANSACTION,
   ISDB8.OWNER,
   ISDB8.ADDRESS,
   ISDB8.VEHICLE,
   ISDB8.TRANSACTION1,
   ISDB8.SHIPMENT,
   ISDB8.SALEINFO,
   ISDB8.FEECHARGED,
   ISDB8.FEESCHEDULE,
   ISDB8.MTAS,
   ISDB8.COUNTY,
   ISDB8.OPERATOR,
   ISDB8.VM_SHIPMENTSTATUS,
   ISDB8.OFFICE
WHERE
   ISDB8.MVTITLESUBTX.SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID
   AND ISDB8.MVTITLESUBTX.SUBTRANSACTIONID = ISDB8.OWNER.OWNERSUBTRANSACTIONINVERSE_S23
   AND ISDB8.VEHICLE.VEHICLEID = ISDB8.MVTITLESUBTX.VEHICLE_VEHICLEID
   AND ISDB8.ADDRESS.SUBTRAN_SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID
   AND ISDB8.TRANSACTION1.TRANSACTIONID = ISDB8.SUBTRANSACTION.TRANSACTION_TRANSACTIONID
   AND ISDB8.SHIPMENT.SHIPMENTID = ISDB8.TRANSACTION1.SHIPMENT_SHIPMENTID
   AND ISDB8.SALEINFO.VEHICLE_VEHICLEID = ISDB8.VEHICLE.VEHICLEID
   AND ISDB8.FEECHARGED.FEECHARGESSUBTRANSACTIONINVE30 = ISDB8.MVTITLESUBTX.SUBTRANSACTIONID
   AND ISDB8.FEECHARGED.FEESCHEDULE_FEESCHEDULEID = ISDB8.FEESCHEDULE.FEESCHEDULEID
   AND ISDB8.FEESCHEDULE.MTAS_MTASID = ISDB8.MTAS.MTASID
   AND ISDB8.ADDRESS.COUNTY_COUNTYID = ISDB8.COUNTY.COUNTYID
   AND ISDB8.TRANSACTION1.OPERATOR_OPERATORID = ISDB8.OPERATOR.OPERATORID
   AND ISDB8.VM_SHIPMENTSTATUS.ID = ISDB8.SHIPMENT.SHIPMENTSTATUSID
   AND
ISDB8.SUBTRANSACTION.OFFICENUMBER = ISDB8.OFFICE.OFFICENUMBER
AND
   ISDB8.MVTITLESUBTX.SITECHANGEREASONID IS NOT NULL
   AND
   ISDB8.OWNER.DISPLAYNAME IS NOT NULL
   AND
   (ISDB8.ADDRESS.ADDRESSTYPEID =6
   OR
   ISDB8.ADDRESS.ADDRESSTYPEID =3)
   AND
   ISDB8.FEESCHEDULE.FEETYPEID =22
   AND
   ISDB8.FEESCHEDULE.FEEUNITID =1;
0
us1975mc
Asked:
us1975mc
  • 7
  • 3
1 Solution
 
momi_sabagCommented:
in order to get it in one row you will have to use a self join (join the address table twice)
the problem is that if a user has 3 addresses, you need to join 3 times etc (or decide which 2 you want out of those 3)
0
 
us1975mcAuthor Commented:
Thanks.  I have heard of self joins, I am not real familiar with them.  Can you explain or have a mini sample that you can show?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
The SQL will write a lot easier if you replace the old style inner joins (table names separated by commas) with the explicit INNER JOIN clause.  Then you can LEFT JOIN the address table to the results, once for each address that you expect.  Filter the LEFT JOIN each time the address is joined so that each column in the result is sourced from a different row in the address table.


Kent
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
us1975mcAuthor Commented:
Would I inner join all my tables that I have?  Do you have a sample of what I am trying to acheive?
0
 
Kent OlsenData Warehouse Architect / DBACommented:

The short version.  :)


SELECT
   ISDB8.MVTITLESUBTX.SITECHANGEDESCRIPTION, ISDB8.MVTITLESUBTX.SITECHANGEREASONID, ISDB8.MVTITLESUBTX.STRGISRETURNEDSITE,
   ISDB8.SUBTRANSACTION.CREATEDON, ISDB8.SUBTRANSACTION.SUBTRANSNUMBER,
ISDB8.SUBTRANSACTION.OFFICENUMBER,
 ISDB8.OWNER.LASTNAME,  ISDB8.OWNER.FIRSTNAME, ISDB8.OWNER.MIDDLENAME, ISDB8.OWNER.SUFFIX1,
   ISDB8.ADDRESS.ADDRESSLINE1, ISDB8.ADDRESS.CITY, ISDB8.ADDRESS.STATE1,
   ISDB8.ADDRESS.ZIP, ISDB8.VEHICLE.YEAR1, ISDB8.VEHICLE.MAKE,
   ISDB8.VEHICLE.VIN, ISDB8.TRANSACTION1.TRANSNUMBER, ISDB8.SHIPMENT.SHIPMENTNUMBER,
   ISDB8.SHIPMENT.SHIPMENTDATE, ISDB8.SALEINFO.NETPRICE, ISDB8.FEECHARGED.FEEAMOUNT,
   ISDB8.FEESCHEDULE.FEETYPEID, ISDB8.FEESCHEDULE.FEEUNITID, ISDB8.FEESCHEDULE.FEEAMOUNT as SCH_Fee_Amt,
   ISDB8.MTAS.MTASCODE, ISDB8.MTAS.DESCRIPTION as MTAS_DESC, ISDB8.COUNTY.COUNTYNAME,
   ISDB8.OPERATOR.OPERATORNUMBER, ISDB8.VM_SHIPMENTSTATUS.DESCRIPTION,
ISDB8.OFFICE.OFFICENAME
FROM ISDB8.MVTITLESUBTX
INNER JOIN ISDB8.SUBTRANSACTION
  ON {keys}
INNER JOIN ISDB8.OWNER
  ON {keys}
INNER JOIN ISDB8.VEHICLE
  ON {keys}
INNER JOIN ISDB8.TRANSACTION1
  ON {keys}
INNER JOIN ISDB8.SHIPMENT
  ON {keys}
.. etc ..
LEFT JOIN ISDB8.ADDRESS address1
  ON {keys}
LEFT JOIN ISDB8.ADDRESS address2
  ON {keys}

WHERE

Open in new window

0
 
us1975mcAuthor Commented:
So to get the left join, I need to join it on the results from the inner join?  Is that correct?
SELECT 
   ISDB8.MVTITLESUBTX.SITECHANGEDESCRIPTION, ISDB8.MVTITLESUBTX.SITECHANGEREASONID, ISDB8.MVTITLESUBTX.STRGISRETURNEDSITE, 
   ISDB8.SUBTRANSACTION.CREATEDON, ISDB8.SUBTRANSACTION.SUBTRANSNUMBER,
ISDB8.SUBTRANSACTION.OFFICENUMBER, 
 ISDB8.OWNER.LASTNAME,  ISDB8.OWNER.FIRSTNAME, ISDB8.OWNER.MIDDLENAME, ISDB8.OWNER.SUFFIX1, 
   ISDB8.ADDRESS.ADDRESSLINE1, ISDB8.ADDRESS.CITY, ISDB8.ADDRESS.STATE1, 
   ISDB8.ADDRESS.ZIP, ISDB8.VEHICLE.YEAR1, ISDB8.VEHICLE.MAKE, 
   ISDB8.VEHICLE.VIN, ISDB8.TRANSACTION1.TRANSNUMBER, ISDB8.SHIPMENT.SHIPMENTNUMBER, 
   ISDB8.SHIPMENT.SHIPMENTDATE, ISDB8.SALEINFO.NETPRICE, ISDB8.FEECHARGED.FEEAMOUNT, 
   ISDB8.FEESCHEDULE.FEETYPEID, ISDB8.FEESCHEDULE.FEEUNITID, ISDB8.FEESCHEDULE.FEEAMOUNT as SCH_Fee_Amt, 
   ISDB8.MTAS.MTASCODE, ISDB8.MTAS.DESCRIPTION as MTAS_DESC, ISDB8.COUNTY.COUNTYNAME, 
   ISDB8.OPERATOR.OPERATORNUMBER, ISDB8.VM_SHIPMENTSTATUS.DESCRIPTION, 
ISDB8.OFFICE.OFFICENAME
FROM 
   

ISDB8.MVTITLESUBTX
INNER JOIN  ISDB8.SUBTRANSACTION 
ON ISDB8.MVTITLESUBTX.SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID, 
INNER JOIN  ISDB8.OWNER
ON ISDB8.MVTITLESUBTX.SUBTRANSACTIONID = ISDB8.OWNER.OWNERSUBTRANSACTIONINVERSE_S23
INNER JOIN ISDB8.VEHICLE
  ON  ISDB8.VEHICLE.VEHICLEID = ISDB8.MVTITLESUBTX.VEHICLE_VEHICLEID
INNER JOIN ISDB8.TRANSACTION1
  ON ISDB8.TRANSACTION1.TRANSACTIONID = ISDB8.SUBTRANSACTION.TRANSACTION_TRANSACTIONID
INNER JOIN ISDB8.SHIPMENT
  ON ISDB8.SHIPMENT.SHIPMENTID = ISDB8.TRANSACTION1.SHIPMENT_SHIPMENTID
INNER JOIN ISDB8.SALEINFO
ON ISDB8.SALEINFO.VEHICLE_VEHICLEID = ISDB8.VEHICLE.VEHICLEID
INNER JOIN  ISDB8.FEECHARGED
ON ISDB8.FEECHARGED.FEECHARGESSUBTRANSACTIONINVE30 = ISDB8.MVTITLESUBTX.SUBTRANSACTIONID
INNER JOIN ISDB8.FEESCHEDULE
ON  ISDB8.FEECHARGED.FEESCHEDULE_FEESCHEDULEID = ISDB8.FEESCHEDULE.FEESCHEDULEID
INNER JOIN ISDB8.MTAS
ON ISDB8.FEESCHEDULE.MTAS_MTASID = ISDB8.MTAS.MTASID
INNER JOIN ISDB8.COUNTY
ON ISDB8.ADDRESS.COUNTY_COUNTYID = ISDB8.COUNTY.COUNTYID
INNER JOIN ISDB8.OPERATOR
ON ISDB8.TRANSACTION1.OPERATOR_OPERATORID = ISDB8.OPERATOR.OPERATORID
INNER JOIN  ISDB8.VM_SHIPMENTSTATUS
ON ISDB8.VM_SHIPMENTSTATUS.ID = ISDB8.SHIPMENT.SHIPMENTSTATUSID
INNER JOIN ISDB8.OFFICE
ON ISDB8.SUBTRANSACTION.OFFICENUMBER = ISDB8.OFFICE.OFFICENUMBER

LEFT JOIN ISDB8.ADDRESS address1
  ON {keys}
LEFT JOIN ISDB8.ADDRESS address2
  ON {keys}
  
WHERE
   
   ISDB8.MVTITLESUBTX.SITECHANGEREASONID IS NOT NULL
  
   AND
   (ISDB8.ADDRESS.ADDRESSTYPEID =6
   OR
   ISDB8.ADDRESS.ADDRESSTYPEID =3)
   AND
   ISDB8.FEESCHEDULE.FEETYPEID =22
   AND
   ISDB8.FEESCHEDULE.FEEUNITID =1;

Open in new window

0
 
Kent OlsenData Warehouse Architect / DBACommented:

Yes.  The joins will occur in the order that they appear in the source.  (Actually, the optimizer may alter the order internally for better performance, but the returned results will be what the source suggests.)  Put all of the INNER JOINS first, then the two LEFT JOINS.


Kent
0
 
us1975mcAuthor Commented:
So something like this:


ISDB8.MVTITLESUBTX
INNER JOIN  ISDB8.SUBTRANSACTION 
ON ISDB8.MVTITLESUBTX.SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID 
INNER JOIN  ISDB8.OWNER
ON ISDB8.MVTITLESUBTX.SUBTRANSACTIONID = ISDB8.OWNER.OWNERSUBTRANSACTIONINVERSE_S23
INNER JOIN ISDB8.VEHICLE
  ON  ISDB8.VEHICLE.VEHICLEID = ISDB8.MVTITLESUBTX.VEHICLE_VEHICLEID
INNER JOIN ISDB8.TRANSACTION1
  ON ISDB8.TRANSACTION1.TRANSACTIONID = ISDB8.SUBTRANSACTION.TRANSACTION_TRANSACTIONID
INNER JOIN ISDB8.SHIPMENT
  ON ISDB8.SHIPMENT.SHIPMENTID = ISDB8.TRANSACTION1.SHIPMENT_SHIPMENTID
INNER JOIN ISDB8.SALEINFO
ON ISDB8.SALEINFO.VEHICLE_VEHICLEID = ISDB8.VEHICLE.VEHICLEID
INNER JOIN  ISDB8.FEECHARGED
ON ISDB8.FEECHARGED.FEECHARGESSUBTRANSACTIONINVE30 = ISDB8.MVTITLESUBTX.SUBTRANSACTIONID
INNER JOIN ISDB8.FEESCHEDULE
ON  ISDB8.FEECHARGED.FEESCHEDULE_FEESCHEDULEID = ISDB8.FEESCHEDULE.FEESCHEDULEID
INNER JOIN ISDB8.MTAS
ON ISDB8.FEESCHEDULE.MTAS_MTASID = ISDB8.MTAS.MTASID
INNER JOIN ISDB8.COUNTY
ON ISDB8.ADDRESS.COUNTY_COUNTYID = ISDB8.COUNTY.COUNTYID
INNER JOIN ISDB8.OPERATOR
ON ISDB8.TRANSACTION1.OPERATOR_OPERATORID = ISDB8.OPERATOR.OPERATORID
INNER JOIN  ISDB8.VM_SHIPMENTSTATUS
ON ISDB8.VM_SHIPMENTSTATUS.ID = ISDB8.SHIPMENT.SHIPMENTSTATUSID
INNER JOIN ISDB8.OFFICE
ON ISDB8.SUBTRANSACTION.OFFICENUMBER = ISDB8.OFFICE.OFFICENUMBER

LEFT JOIN ISDB8.ADDRESS address1
  ON ISDB8.MVTITLESUBTX.SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID
LEFT JOIN ISDB8.ADDRESS as address2
 ON ISDB8.MVTITLESUBTX.SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID

Open in new window

0
 
us1975mcAuthor Commented:
This is the error I receive with the above post code.      
ERROR.png
0
 
us1975mcAuthor Commented:
OK I am close with this query.  Any suggestions?  I get the following error:

AN ON CLAUSE IS INVALID. SQLCODE=-338, SQLSTATE=42972, DRIVER=3.61.65
SELECT 
   ISDB8.MVTITLESUBTX.SITECHANGEDESCRIPTION, ISDB8.MVTITLESUBTX.SITECHANGEREASONID, ISDB8.MVTITLESUBTX.STRGISRETURNEDSITE, 
   ISDB8.SUBTRANSACTION.CREATEDON, ISDB8.SUBTRANSACTION.SUBTRANSNUMBER,
ISDB8.SUBTRANSACTION.OFFICENUMBER, 
 ISDB8.OWNER.LASTNAME,  ISDB8.OWNER.FIRSTNAME, ISDB8.OWNER.MIDDLENAME, ISDB8.OWNER.SUFFIX1, 
   ISDB8.ADDRESS.ADDRESSLINE1, ISDB8.ADDRESS.CITY, ISDB8.ADDRESS.STATE1, 
   ISDB8.ADDRESS.ZIP, ISDB8.VEHICLE.YEAR1, ISDB8.VEHICLE.MAKE, 
   ISDB8.VEHICLE.VIN, ISDB8.TRANSACTION1.TRANSNUMBER, ISDB8.SHIPMENT.SHIPMENTNUMBER, 
   ISDB8.SHIPMENT.SHIPMENTDATE, ISDB8.SALEINFO.NETPRICE, ISDB8.FEECHARGED.FEEAMOUNT, 
   ISDB8.FEESCHEDULE.FEETYPEID, ISDB8.FEESCHEDULE.FEEUNITID, ISDB8.FEESCHEDULE.FEEAMOUNT as SCH_Fee_Amt, 
   ISDB8.MTAS.MTASCODE, ISDB8.MTAS.DESCRIPTION as MTAS_DESC, ISDB8.COUNTY.COUNTYNAME, 
   ISDB8.OPERATOR.OPERATORNUMBER, ISDB8.VM_SHIPMENTSTATUS.DESCRIPTION, 
ISDB8.OFFICE.OFFICENAME
FROM 
   

ISDB8.MVTITLESUBTX
INNER JOIN  ISDB8.SUBTRANSACTION 
ON ISDB8.MVTITLESUBTX.SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID
INNER JOIN  ISDB8.OWNER
ON ISDB8.MVTITLESUBTX.SUBTRANSACTIONID = ISDB8.OWNER.OWNERSUBTRANSACTIONINVERSE_S23
INNER JOIN ISDB8.VEHICLE
ON  ISDB8.VEHICLE.VEHICLEID = ISDB8.MVTITLESUBTX.VEHICLE_VEHICLEID
INNER JOIN ISDB8.TRANSACTION1
ON ISDB8.TRANSACTION1.TRANSACTIONID = ISDB8.SUBTRANSACTION.TRANSACTION_TRANSACTIONID
INNER JOIN ISDB8.SHIPMENT
ON ISDB8.SHIPMENT.SHIPMENTID = ISDB8.TRANSACTION1.SHIPMENT_SHIPMENTID
INNER JOIN ISDB8.SALEINFO
ON ISDB8.SALEINFO.VEHICLE_VEHICLEID = ISDB8.VEHICLE.VEHICLEID
INNER JOIN  ISDB8.FEECHARGED
ON ISDB8.FEECHARGED.FEECHARGESSUBTRANSACTIONINVE30 = ISDB8.MVTITLESUBTX.SUBTRANSACTIONID
INNER JOIN ISDB8.FEESCHEDULE
ON  ISDB8.FEECHARGED.FEESCHEDULE_FEESCHEDULEID = ISDB8.FEESCHEDULE.FEESCHEDULEID
INNER JOIN ISDB8.MTAS
ON ISDB8.FEESCHEDULE.MTAS_MTASID = ISDB8.MTAS.MTASID
INNER JOIN ISDB8.COUNTY
ON ISDB8.ADDRESS.COUNTY_COUNTYID = ISDB8.COUNTY.COUNTYID
INNER JOIN ISDB8.OPERATOR
ON ISDB8.TRANSACTION1.OPERATOR_OPERATORID = ISDB8.OPERATOR.OPERATORID
INNER JOIN  ISDB8.VM_SHIPMENTSTATUS
ON ISDB8.VM_SHIPMENTSTATUS.ID = ISDB8.SHIPMENT.SHIPMENTSTATUSID
INNER JOIN ISDB8.OFFICE
ON ISDB8.SUBTRANSACTION.OFFICENUMBER = ISDB8.OFFICE.OFFICENUMBER

LEFT JOIN ISDB8.ADDRESS ADDRESS1
ON  ISDB8.ADDRESS.SUBTRAN_SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID
LEFT JOIN ISDB8.ADDRESS ADDRESS2
ON  ISDB8.ADDRESS.SUBTRAN_SUBTRANSACTIONID = ISDB8.SUBTRANSACTION.SUBTRANSACTIONID
  
WHERE
   ADDRESS1.ADDRESSTYPEID = 6
   AND
   ADDRESS2.ADDRESSTYPEID = 3
   AND
   ISDB8.MVTITLESUBTX.SITECHANGEREASONID IS NOT NULL
    
   AND
   ISDB8.FEESCHEDULE.FEETYPEID =22
   AND
   ISDB8.FEESCHEDULE.FEEUNITID =1;

Open in new window

0
 
us1975mcAuthor Commented:
Thank you very much.  You have answered my one question.  I will do another post to get my other question answered about the error I am receiving with the query.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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