?
Solved

Many selection

Posted on 2003-03-21
10
Medium Priority
?
177 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

 
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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
Suggested Courses

752 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