Solved

SQL Union Query

Posted on 2015-02-19
3
122 Views
Last Modified: 2015-02-19
I have this case.

I want to get all records from a table called workorder where a column called datepaid is within a range, then all rows from a table called wo_adjs where the column woid is equal to the corresponding column in the table workorder called wokey and the colun in wo_adjs = 'L'.

Lets assume the date range is 2015-02-01 to 2015-02-16.

Can someone show me a sql query to do this?

The structure of both tables is attached.
table-wo-adjs.jpg
table-workorder.jpg
0
Comment
Question by:Richard Korts
  • 2
3 Comments
 
LVL 11

Expert Comment

by:Dany Balian
Comment Utility
Select * from workorder where datepaid between 2015-02-01 and 2015-02-16
Union
Select workorder.* from workorder
inner join wo_adjs on wo_adjs.whoid=workorder.wokey
where wo_adjs.type='L'
0
 

Author Comment

by:Richard Korts
Comment Utility
To Dany Balian,

Doesn't work, I modified it slightly to omit Workorders where wokey > 200000.

So it looks like this:

Select * from workorder where datepaid >= '2015-02-01'  and datepaid <= '2015-02-16' and wokey < 200000
Union
Select workorder.* from workorder
inner join wo_adjs on wo_adjs.whoid=workorder.wokey
where wo_adjs.type='L'

It produces 119 rows.

If I just look at the table wo_adjs for type = 'L' with woid < 200000 I get 6 rows.
0
 
LVL 11

Accepted Solution

by:
Dany Balian earned 500 total points
Comment Utility
So u need only 6 rows in total? If yes then u don't need union!

Select workorder.* from workorder
inner join wo_adjs on wo_adjs.whoid=workorder.wokey
where wo_adjs.type='L'
And datepaid >= '2015-02-01'  and datepaid <= '2015-02-16' and wokey < 200000

Can u give us a sample data and expected result?
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now