Jump to content
  • Advertisement
Sign in to follow this  
md_lasalle

Query from 2 tables

This topic is 4734 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hi, I have a Table A and a Table B, what kind of query would i need to get all records from Table A AND Table B Ordered by Date? Both Tables have a Column called Date. I'm using an Access database with vb6 Thank you very much.

Share this post


Link to post
Share on other sites
Advertisement
the problem with "SELECT * FROM TABLE_A, TABLE_B ORDER BY DATE"

is that i have a Where clause that must choose items from the current Month, so with one table it's easy :

"Select * from TableA Where MONTH(cdate(DateEntry)) = " & CurrentMonth & " order by cdate(DateEntry) DESC;"

but with two tables he wants something like this :

"Select *
from TableA,TableB
Where MONTH(cdate(TableA.DateEntry)) = " & CurrentMonth & " And MONTH(cdate(TableB.DateEntry)) = " & CurrentMonth & "
order by cdate(TableA.DateEntry) and cdate(TableB.DateEntry) DESC;"

But the results are weird. Can someone point me out in the good direction thank you.

Share this post


Link to post
Share on other sites
Guest Anonymous Poster
Assuming that the tables have an identical structure, you could try something like this:

SELECT * FROM TableA
WHERE MONTH(cdate(DateEntry)) = " & CurrentMonth & "

UNION ALL

SELECT * FROM TableB
WHERE MONTH(cdate(DateEntry)) = " & CurrentMonth & "

ORDER BY cdate(DateEntry) DESC;


Note: Untested for syntax errors...

Share this post


Link to post
Share on other sites
VB give me some probleme with the Order By... it says that only Fields from the FIRST query can be in the Order by clause... so i tried to put the tableName.theField in the query without succes...

Share this post


Link to post
Share on other sites
Guest Anonymous Poster
How about specifying the column to sort on explicitly?

e.g.

SELECT cdate(DateEntry) AS myColToSort, *
FROM TableA
WHERE MONTH(cdate(DateEntry)) = " & CurrentMonth & "

UNION ALL

SELECT cdate(DateEntry) AS myColToSort, *
FROM TableB
WHERE MONTH(cdate(DateEntry)) = " & CurrentMonth & "

ORDER BY myColToSort DESC


The above seems to work alright in SQL Server

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

Participate in the game development conversation and more when you create an account on GameDev.net!

Sign me up!