reading data from different tables

Customer Support Database Support
1

hey, I am having a problem (obviously)..
I am trying to make an asp page where it “reads” data from (lets say) TableX and displays it in a table..

What I am trying to do next is to take (lets say) “Names” from TableX and compare it to “Names” in TableY. If the name of TableX is the same as the name in TableY, I want it to display “Info” from TableY.. Otherwise, just display the name from TableX…

So the problem is opening two tables, from the same database and checking if they have the same records..

I am new to these kind of stuff, so I would appreciate you help :slight_smile:

2

if I understand you right, your query should look like this in some way :

SELECT x.Names, y.Info FROM TableX x, TableY y WHERE x.Names = y.Names

But I’m not sure if I got your question correctly … :frowning:

Regards,
Niels

3

[QUOTE=Scott]
if I understand you right, your query should look like this in some way :

SELECT x.Names, y.Info FROM TableX x, TableY y WHERE x.Names = y.Names

But I’m not sure if I got your question correctly … :frowning:

Regards,
Niels
[/QUOTE]

cheers mate, I’m going to try it and let you know if it worked the way I wanted it :slight_smile:

4

SELECT x.Names, y.Info FROM TableX x, TableY y WHERE x.Names = y.Names

hm.. I cant make it work..

in x.Names “x” is the name of the table, e.g. senzafine
and the same applies to y.Info

“FROM TableX x, TableY y” you said.. if TableX is the name of table “x” and TableY the name of table “y”, then what does the “x” and “y” represent (in bold)??

5

Just post the structure of your tables and explain, in English, what you want to accomplish… between us, we can probably solve any SQL riddle… :slight_smile:
-Dave

6

Also, please state whether this is an MS-Access, MS-SQL or other type of database (there are syntactical differences).

riley

7

Assuming MS SQL below, or any SQL-99 compliant database.

I’m not entirely sure what you need, but the query to use depends on how the relation to Names in x and y is. If there is a one to many relation (a name can be in y multiple times), one or the other will produce different results.

Consider table x:
Adam
Ben
Cecil
Dominic

Table y:
Adam, Info for Adam
Dominic, Info for Dominic
Dominic, More info for Dominic

SELECT Names, (SELECT TOP 1 Info FROM y WHERE y.Names = x.Names) As Info FROM x

This should return a recordset with all the names from x, and a second column with the Info from y if there is any matching record. If there are more than one, the first one will be used:

Adam, Info for Adam
Ben,
Cecil,
Dominic, Info for Dominic

SELECT x.Names, y.Info FROM x LEFT OUTER JOIN y ON x.Names = y.Names

This one will duplicate the name from x for every entry in y:

Adam, Info for Adam
Ben,
Cecil,
Dominic, Info for Dominic
Dominic, More info for Dominic