Solved

SQL Union Query

Posted on 2015-02-19
3
124 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
ID: 40620213
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
ID: 40620232
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
ID: 40620244
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

920 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

14 Experts available now in Live!

Get 1:1 Help Now