Reference Table and Display related Data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Reference Table and Display related Data

Author

Topic

code2break
Starting Member

2 Posts

Posted - 2010-03-15 : 14:51:18

Hi everyone,
Im new to SQL. I have a SQL Query

 $pub_query = "SELECT pub.miniref, pub.title, dbxref.accession " .
                  "FROM pub " .
                  "JOIN pub_dbxref ON pub.pub_id = pub_dbxref.pub_id " .
                  "JOIN dbxref ON pub_dbxref.dbxref_id = dbxref.dbxref_id " .
                  "JOIN db ON dbxref.db_id = db.db_id " .
                  "WHERE pub.pub_id = %d AND db.name = 'PMID'";

which outputs $pub_query

How can I take that and look at another table with some colums and if it matches the PMID colum the related other data to display

for example lets say

pmid | related_id
xxxx | yyyyyy

if the $pub_query= matches the xxxx display or output yyyyy.

Hope I'm clear on this.
Thank you so much in advance.

ray-SQL
Starting Member

18 Posts

Posted - 2010-03-15 : 15:42:27

Hi,

If there is not some special reason where you must do this in two stages, you can just expand your existing code:


SELECT pub.miniref, pub.title, dbxref.accession, T.Related_ID 
FROM pub 
JOIN pub_dbxref ON pub.pub_id = pub_dbxref.pub_id 
JOIN dbxref ON pub_dbxref.dbxref_id = dbxref.dbxref_id
JOIN db ON dbxref.db_id = db.db_id 
LEFT JOIN ANOTHERTABLE T on pub.PMID = T.PMID
WHERE pub.pub_id = %d AND db.name = 'PMID'

Since I don't know if there is always a match in your "Another Table", I used left join to ensure the existing query still returned all its results.

Please let me know if you have any questions.

Ray Dai

code2break
Starting Member

2 Posts

Posted - 2010-03-15 : 16:17:46

Hi Thank you, but I think I forgot to mention the reference table is in a different database thus cant i get the out put from my query and use it to see related data on another database. I assume there will be some php involved.

lets say

$pub_query = "SELECT pub.miniref, pub.title, dbxref.accession " .
"FROM pub " .
"JOIN pub_dbxref ON pub.pub_id = pub_dbxref.pub_id " .
"JOIN dbxref ON pub_dbxref.dbxref_id = dbxref.dbxref_id " .
"JOIN db ON dbxref.db_id = db.db_id " .
"WHERE pub.pub_id = %d AND db.name = 'PMID'";

so I have $pub_query. Nowall I need is to I need to look for that in the "B" database table "PMID" and output the relater ID associated with it. (don't worry about the database login issues).

Thank you so much.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-03-15 : 16:48:25

you can reference another table using 3 or 4 part naming convention. If it's on the same server, use databasename.schemaowner.tablename. If it's on another server, simply add servername to the front of the naming convention: servername.databasename.schemaowner.tablename

Terry

-- Procrastinate now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources