Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Union SQL revision 2

Status
Not open for further replies.

trustsun

Technical User
Feb 4, 2004
73
US
Could someone revise this Union SQL; I need to match or only see field "jobdatestr" from the table "tblsrvjobCTN" when joined.



SELECT jobdatestr, jobdateend, startdatetime, enddatetime, job, jobnum, userid
FROM tblnitesCTN;
UNION SELECT jobdatestr, jobdateend, startdatetime, enddatetime, jobs, jobnum, userid
FROM tblsrvjobCTN;
 
How about something like:
Code:
SELECT jobdatestr, tbl_job.jobdatestr, jobdateend, startdatetime, enddatetime, job, jobnum, userid
FROM tblnitesCTN, tblsrvjobCTN tbl_Job
WHERE yourjoinfield (e.g. Jobnum) = tbl_Job.yourjoinfield
Or is that nothing like what you are after??

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.
 
Sorry that did'nt do it;

I only need to see data where the dates match from the field "jobdatestr" using a union quey from each table.


SELECT jobdatestr, jobdateend, startdatetime, enddatetime, job, jobnum, userid
FROM tblnitesMAC;
UNION SELECT jobdatestr, jobdateend, startdatetime, enddatetime, jobs, jobnum, userid
FROM tblsrvjobMAC;
 
SELECT A.jobdatestr, A.jobdateend, A.startdatetime, A.enddatetime, A.job, A.jobnum, A.userid
FROM tblnitesMAC A INNER JOIN tblsrvjobMAC B ON A.jobdatestr = B.jobdatestr
UNION ALL SELECT A.jobdatestr, A.jobdateend, A.startdatetime, A.enddatetime, A.job, A.jobnum, A.userid
FROM tblsrvjobMAC A INNER JOIN tblnitesMAC B
ORDER BY 1;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Looks like PHV's solution should work for you except that you may not need the UNION. The SQL
Code:
SELECT A.jobdatestr, A.jobdateend, A.startdatetime, A.enddatetime, A.job, A.jobnum, A.userid
FROM tblnitesMAC A INNER JOIN tblsrvjobMAC B ON A.jobdatestr = B.jobdatestr
from PHV's code will give you matching jobdatestr records that occur in both tables without requiring a UNION.

BTW: The table names seem to have changed from your first example to the second. Any significance to that?
 
PHV is the example I would rather use, but I have the error, "Syntax error in FROM clause". Please advise!
 
Can you please post your actual SQL code and the real table's names ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
This might be a classic schoolboy error on my part but do you need an 'ON A.jobdatestr = B.jobdatestr' in the second select in the union query?

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.
 
Good catch HarleyQuinn !
trustsun, sorry for the typo:
SELECT A.jobdatestr, A.jobdateend, A.startdatetime, A.enddatetime, A.job, A.jobnum, A.userid
FROM tblnitesMAC A INNER JOIN tblsrvjobMAC B ON A.jobdatestr = B.jobdatestr
UNION ALL SELECT A.jobdatestr, A.jobdateend, A.startdatetime, A.enddatetime, A.job, A.jobnum, A.userid
FROM tblsrvjobMAC A INNER JOIN tblnitesMAC B ON A.jobdatestr = B.jobdatestr
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top