2 Replies - 786 Views - Last Post: 01 July 2011 - 07:32 AM

#1 feedz87  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 26-May 11

query problem

Posted 03 June 2011 - 12:34 AM

Posted Image


What i want is how to create query for 4 table.The below code is old query from 3 table such as JobOrderHeader,JobOrderLine and CustomerCharges.But 1 have added 1 table is JobOrderCargos.The result i want is when the JobOrderCargos.IIDNo = "No data in database" the result will be show.Anyone can help me how to join table of JobOrderCargos to the below query.

StrSQLQ1 = "SELECT " & _
    "CustomerCharges.JobOrderID, CustomerCharges.lCientID, CustomerCharges.ActivityDate, CustomerCharges.TaskName, CustomerCharges.Charges, CustomerCharges.LineSeqID, CustomerCharges.IIDNo ,CustomeCustomerCharges.consignee CustomerCharges.marking, CustomerCharges.rate, CustomerCharges.m3, CustomerCharges.tonn,CustomerCharges.labor, CustomerCharges.storg, CustomerCharges.overtm, " & _
    "JobOrderHeader.JobType, JobOrderHeader.CustomerName, JobOrderHeader.LedgerNo, " & _
    "JobOrderLine.LorryOutDt, JobOrderLine.FL, JobOrderLine.VesselIn, JobOrderLine.ContainerTruckNo, JobOrderLine.ContainerSz, JobOrderLine.SSRNo " & _
    "From " & _
    " { oj (WMS.dbo.JobOrderLine JobOrderLine INNER JOIN WMS.dbo.CustomerCharges CustomerCharges ON " & _
    "JobOrderLine.JobOrderID = CustomerCharges.JobOrderID AND " & _
    "JobOrderLine.LineSeqID = CustomerCharges.LineSeqID) " & _
    "INNER JOIN WMS.dbo.JobOrderHeader JobOrderHeader ON " & _
        "JobOrderLine.JobOrderID = JobOrderHeader.JobOrderID AND JobOrderLine.deletion_flag <> 'D' AND " & _
        " " & stringwithoption & _
        " " & billstatus & _
    "AND JobOrderHeader.JobType =  '" & Left$(job, 6) & "' AND CustomerCharges.dgrefno = 'None'} " & _
    "Order By " & _
    "CustomerCharges.ClientID ASC, " & _
    "CustomerCharges.JobOrderID ASC," & _
    "CustomerCharges.LineSeqID ASC "


Is This A Good Question/Topic? 0
  • +

Replies To: query problem

#2 SurfingShark  Icon User is offline

  • D.I.C Head

Reputation: 38
  • View blog
  • Posts: 188
  • Joined: 18-April 11

Re: query problem

Posted 24 June 2011 - 07:44 PM

Reading your visual representation is almost as painstaking as reading your coded query. Please provide more detailed verbose execution as to what you're trying to accomplish.

It really looks like you just need to do a multiple join statement with a WHERE clause to pluck out the data you need.

Something like:

SELECT whateverfieldsyouwant
FROM YourFirstTable first
INNER JOIN YourSecondTable second
ON first.connectingfield = second.connectingfield
INNER JOIN YourThirdTable third
ON first.connectingfield = third.connectingfield
INNER JOIN YourFourthTable fourth
ON second.connectingfield = fourth.connectingfield
WHERE fourth.IIDNo = 'No data in database'


That's obviously not using your table structure, but you should get the idea.
Was This Post Helpful? 0
  • +
  • -

#3 samuraitux  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 9
  • View blog
  • Posts: 65
  • Joined: 29-April 09

Re: query problem

Posted 01 July 2011 - 07:32 AM

First I would list the columns that you want from each table. Then we can help build a query. But from the looks of your code you are just grabbing every single column from all 4 tables.

Probably a better way to phrase this is what are you expecting from your DATA SET. That way when you pull your requested data you will be able to say yes this is the data I was looking for or no this SET is not correct.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1