Union SQL revision 2

[trustsun]

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;

 

[HarleyQuinn]

How about something like:

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.

 

[trustsun]

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;

 

[PHV]

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

 

[Golom]

Looks like PHV's solution should work for you except that you may not need the UNION. The SQL

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?

 

[trustsun]

PHV is the example I would rather use, but I have the error, "Syntax error in FROM clause". Please advise!

 

[PHV]

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

 

[HarleyQuinn]

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.

 

[PHV]

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

 

[trustsun]

Thanks guys, your the greatest!

trust