Avatar of us1975mc
us1975mcFlag for United States of America

asked on 

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;
.NET ProgrammingDB2Visual Basic.NET

Avatar of undefined
Last Comment
us1975mc
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

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)
Avatar of us1975mc
us1975mc
Flag of United States of America image

ASKER

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?
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

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
Avatar of us1975mc
us1975mc
Flag of United States of America image

ASKER

Would I inner join all my tables that I have?  Do you have a sample of what I am trying to acheive?
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image


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

Avatar of us1975mc
us1975mc
Flag of United States of America image

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of us1975mc
us1975mc
Flag of United States of America image

ASKER

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

Avatar of us1975mc
us1975mc
Flag of United States of America image

ASKER

This is the error I receive with the above post code.      
ERROR.png
Avatar of us1975mc
us1975mc
Flag of United States of America image

ASKER

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

Avatar of us1975mc
us1975mc
Flag of United States of America image

ASKER

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.
.NET Programming
.NET Programming

The .NET Framework is not specific to any one programming language; rather, it includes a library of functions that allows developers to rapidly build applications. Several supported languages include C#, VB.NET, C++ or ASP.NET.

137K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo