selecting specific variables from different tables from SQL

[Bader Bin Adwan]

Hi,

Is it possible to select several specific variables from multiple different tables in SQL server? For instance, If I have table(Demo1) that has variables such CID, Name, and Age, And table (Demo2) that has diagnosis, hospital code, DoB. and I want to retrieve CID and age from Table Demo and retrieve diagnosis and DoB from table Demo2 into one data set to work with, how can I achieve this?

I used the following command:

 
 odbc load, exec(SELECT CID FROM Demo)

and it works but when I tried to retrieve variables from another tables it says I have to start with empty data?

Any suggestion is appreciated!

[Daniel Schaefer]

Is it possible to select several specific variables from multiple different tables in SQL server?

The short answer is yes. You need a join operation.

The slightly longer answer is that this kind of thing is exactly what SQL was made to do. You use multiple tables to avoid storing redundant data with as you might with a single table - particularly in order to represent one to many and many to many relationships. I take it a single person only has one unique id, name, and age, but could have multiple diagnoses and hospital codes? Weird that DoB isn't stored with the first table, but okay.

[Daniel Schaefer]

Wait, your tables are called "Demo1" and "Demo2"? Is this a homework assignment?

[Bader Bin Adwan]

Thanks Daniel for getting back. No this is just to give an example for explaining how that the data is stored and what I want from it. Answering your question, yes each patient has unique identifier which in this case is Civil id (CID).

The command in the website that you provided, works in Stata, right?

[Daniel Schaefer]

The command in the website that you provided, works in Stata, right?

Again, the short answer is "yes". The details are a little complicated, but worth understanding. Consider your code from #1.

odbc load, exec(SELECT CID FROM Demo)

This command will actually send a line of SQL (pronounced like "sequel") to your relational database where it will be executed, then the response will be processed by the command before the data is put in the Stata environment. This portion of your command is actually a line of SQL, not Stata:

SELECT CID FROM Demo

Different databases can use different dialects of SQL, so not every database will allow you to use all of the same commands, nor will the commands necessarily execute in quite the same way. ODBC (which the -odbc- command is named after) is actually a SQL standard. Databases that support obdc support a standard SQL dialect. That said, the -obdc- command doesn't necessarily know that the database it connects to supports the OBDC SQL standard.

The point here is that what you really need is valid SQL, not valid Stata. All that said, the different join operators are so fundamental to SQL (they even exist in nosql database languages!) that any database is going to support these operations.

Finally, just a bit of nitpicking from me: JOIN is a clause, not a command. Also notice there are multiple kinds of JOIN clauses. The differences matter. Take a moment to look through that whole page.

[Daniel Schaefer]

By the way, my question was actually "Can a person have multiple diagnoses and hospital codes?" Sorry if that wasn't clear in #2.

[Bader Bin Adwan]

By the way, my question was actually "Can a person have multiple diagnoses and hospital codes?" Sorry if that wasn't clear in #2.

Yes

[Daniel Schaefer]

In that case, it seems storing DoB in that second table is needlessly increasing the size of your database. Anyway, best of luck learning SQL.