?
Solved

Many selection

Posted on 2003-03-21
10
Medium Priority
?
180 Views
Last Modified: 2010-04-06
Can i do a multiple selection of table in a recordset?


strSQL = "SELECT * FROM soccer ORDER BY newsDate DESC"
strSQL2 = "SELECT * FROM basketball ORDER BY newsDate DESC"

Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.CursorLocation = adUseClient
objRS.Open strSQL, strSQL2, objConn
0
Comment
Question by:Nismo83
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 28

Expert Comment

by:sybe
ID: 8186545
you could try a UNION, but then the fields of the both tables must be exactly the same.

strSQL = "SELECT * FROM soccer ORDER BY newsDate DESC "
strSQL = strSQL & "UNION "
strSQL = strSQL & "SELECT * FROM basketball ORDER BY newsDate DESC "


0
 
LVL 1

Expert Comment

by:disaster77
ID: 8186717
You could try

SELECT * FROM soccer, basketball ORDER BY newDate DESC

that will select all fields from both tables.
0
 

Author Comment

by:Nismo83
ID: 8187009
SELECT * FROM soccer, basketball ORDER BY newDate DESC
does that means that all tables that i gonna UNION must hav the same field name?

strSQL = "SELECT * FROM soccer ORDER BY newsDate DESC "
strSQL = strSQL & "UNION "
strSQL = strSQL & "SELECT * FROM basketball ORDER BY newsDate DESC "
what if there's any differences?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Expert Comment

by:cirtap
ID: 8192542
the UNION requires the fields to have the name names in both tables, but you can rename them in the SELECT statement for each table so the SQL engine knows how to "connect" them

in soccer table
 - player
 - firstname
 - lastname
 - newsDate
in basketball table
 - id
 - first
 - last
 - newsDate

strSQL = "SELECT player AS personID, firstname, lastname, newsDate FROM soccer ORDER BY newsDate DESC "
strSQL = strSQL & "UNION "
strSQL = strSQL & "SELECT id AS personID, first AS firstname, last, newsDate AS lastname FROM basketball ORDER BY newsDate DESC "

so the resulting recordset has three fields personID, firstname, lastname. If the field names differ you must names all fields you need, no *
The corresponding fields must be of the same data type (AFAIK)


CirTap
0
 
LVL 4

Accepted Solution

by:
whammy earned 60 total points
ID: 8199125
Also, do you need to join this data according to some criteria?

If so I'd look into INNER JOIN and possibly LEFT OUTER JOIN. If this is the case, and you need more help, please post - but the basic syntax for records where BOTH fields are present and NOT null in both tables (i.e. related keys), is like:

SELECT A.*,B.something FROM table1 A INNER JOIN table2 B ON (A.ID = B.ID) WHERE B.something = '1'

This would return all records where A.ID = B.ID and B.something is "1".

I'm also using aliases in that (A,B) for the table names (table1, table2) to illustrate their use - if nothing else it saves you from having to type "table1.something, table2.whatever" over and over again in the case that you have long table names...
0
 
LVL 5

Expert Comment

by:cirtap
ID: 8204556
the difference between JOINs and UNION is that you'll get additional columns for the joined fields, whereas a UNION used 1 column for matching fields.
Taking my example from above, a JOIN would usually result in a recordset having 6 columns (with whatever names), whereas the UNION would come up with just 3.
So instead of handling individual columns/fields like "socker_player_name", "basketbal_player_name" etc, a UNION would have a single "player_name" column with all people that "play" something (memberlist of a sports-club).
Big difference.

CirTap
0
 
LVL 4

Expert Comment

by:whammy
ID: 8206937
Yeah, I guess it all depends upon what data you want to retrieve. Although you could still specify which fields you want in your SQL statement when using a JOIN.
0
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 9115273
This question has been classified abandoned. I will make a recommendation to the
moderators on its resolution in a week or two. I appreciate any comments
that would help me to make a recommendation.

<note>
Unless it is clear to me that the question has been answered I will recommend delete.  It is possible that a Grade less than A will be given if no expert makes a case for an A grade. It is assumed that any participant not responding to this request is no longer interested in its final disposition.
</note>

If the user does not know how to close the question, the options are here:
http://www.experts-exchange.com/help/closing.jsp


Cd&

0
 
LVL 5

Expert Comment

by:cirtap
ID: 9115521
Hi Cd&,

there are 24 open question by this user... maybe wanna take a look?

Have fun,
CirTap
0
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 9115594
OHHHHHHHHHH!  some pretty ugly grading too.

I'm not even going to wait for the second pass on this one.  I am posting a request in CS tho have the account suspended.

Cd&
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There are two main kinds of selectors in CSS: One is base selector like h1, h2, body, table or any existing HTML tags.  For instance, the following rule sets all paragraphs (<p> elements) to red: (CODE) CSS also allows us to define our own custom …
Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…
Suggested Courses

621 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