How to join select statements without Union

Question

0.00/5 (No votes)

See more:

SQL

Select j.Ordernumber,s.ShipmentNo,t.TruckNo,j.Route,j.CustomerName,j.Ordertype,
            d.DeliveryDate,d.DeliveryTime,Convert(varchar(35),jt.StausCode)as StausCode,j.JobID,'Active' as PStatus
            from tbl_pms_JobOrders j
            Inner Join tbl_pms_JobOrders_Trucks_Info jt on jt.JobID=j.JobID
            Inner Join tbl_pms_Trucks t on t.TruckID=jt.TruckID
            Inner Join tbl_pms_ShipmentInfo s on s.ShipmentID=jt.ShipmentID
            Inner Join tbl_pms_DeliveryInfo d on d.JobID=j.JobID
            Where j.WarehouseID=@WarehouseID and jt.StausCode != 1 and jt.StausCode !=77
            and j.JobID not in (Select JobID from tbl_pms_POD_Submit)
            and jt.TruckID = @TruckID
            and jt.TruckID != 312
            and d.recno in (select max (recno) from tbl_pms_DeliveryInfo where JobID in ( select JobID from tbl_pms_JobOrders_Trucks_Info where TruckID=@TruckID and JobID in (select JobID from tbl_pms_JobOrders where WarehouseID=@WarehouseID)) group by JobID)

       Union


        Select j.Ordernumber,s.ShipmentNo,t.TruckNo,j.Route,j.CustomerName,j.Ordertype,
            d.DeliveryDate,d.DeliveryTime,Convert(varchar(35),jt.StausCode)as StausCode,j.JobID,'Committed' as PStatus
            from tbl_pms_JobOrders j
            Inner Join tbl_pms_JobOrders_Trucks_Info jt on jt.JobID=j.JobID
            Inner Join tbl_pms_Trucks t on t.TruckID=jt.TruckID
            Inner Join tbl_pms_ShipmentInfo s on s.ShipmentID=jt.ShipmentID
            Inner Join tbl_pms_DeliveryInfo d on d.JobID=j.JobID
            Where j.WarehouseID=@WarehouseID and jt.StausCode != 1 and jt.StausCode !=77
            and j.JobID  in (Select JobID from tbl_pms_POD_Submit)
            and jt.TruckID = @TruckID
            and jt.TruckID != 312
            and d.recno in (select max (recno) from tbl_pms_DeliveryInfo where JobID in ( select JobID from tbl_pms_JobOrders_Trucks_Info where TruckID=@TruckID and JobID in (select JobID from tbl_pms_JobOrders where WarehouseID=@WarehouseID)) group by JobID)

This my query for status of records in joborder table which are not submitted to pod table and which are submitted to pod table .

i made the query in this way Above..

i facing a problem that it is a bit slow .... is there any alternative for above query

plz help me ..Thanks Advance :)

Posted 31-Oct-13 0:34am

DevilsCod

Add a Solution

Comments

Mehdi Gholam 31-Oct-13 6:57am

If your queries are slow, try creating indexes first.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

E.F. Nijboer · Answer 1 · 2013-10-31T02:31:00

The only difference seems to be that it can be found in table tbl_pms_POD_Submit. (Select JobID from tbl_pms_POD_Submit). Instead of a union you could simply put it in a single query and do an outer join on tbl_pms_POD_Submit and select the JobID in the result. When the JobID is filled you know it is active, otherwise it is committed. A big advantage is that you also have the JobId's of the active ones just in case you might need them.

As already mentioned in one of the comments, use the profiles to check for missing indexes.

Good luck!