Oracle - Trying to query data from two tables in the same query

Question

I am trying to query data mainly from one table, but I also need two columns from another table in the same database. A portion of my query looks somewhat like this:

SELECT
      'Contact 1' AS "Phone 2 - Type",
      Emerg_Phone_1 AS "Phone 2 - Value",
      'Contact 2' AS "Phone 3 - Type",
      Emerg_Phone_2 AS "Phone 3 - Value",
      'Student Cell' AS "Phone 4 - Type",
      STUDENTCELL AS "Phone 4 - Value",
FROM STUDENTS JOIN STUDENTS2

So basically, all five selects OTHER THAN the STUDENTCELL are coming from the STUDENTS table, but I need the one column STUDENTCELL from the STUDENTS2 table in my same query. Thank you for any help and advice, I am very new to mySQL and oracle both.

You need to specify the `ON clause` after join. – John Bupit Aug 27 '15 at 13:43 — John Bupit, Aug 27 '15 at 13:43
What is the common column among the two tables? – Arun Palanisamy Aug 27 '15 at 13:49 — Arun Palanisamy, Aug 27 '15 at 13:49

score 1 · Answer 1 · edited May 23 '17 at 11:43

1

You almost got the solution. Except the joining clause.You need to join both tables with common column. So you can either use ON Clause or Using clause. The basic difference b/w these two are

The USING clause

The USING clause is used if several columns share the same name but you don’t want to join using all of these common columns. The columns listed in the USING clause can’t have any qualifiers in the statement, including the WHERE clause:

The ON clause

The ON clause is used to join tables where the column names don’t match in both tables. The join conditions are removed from the filter conditions in the WHERE clause:

I'm considering Common column as ID. If both tables have different column name then use on

SELECT
      'Contact 1' AS "Phone 2 - Type",
      Emerg_Phone_1 AS "Phone 2 - Value",
      'Contact 2' AS "Phone 3 - Type",
      Emerg_Phone_2 AS "Phone 3 - Value",
      'Student Cell' AS "Phone 4 - Type",
      STUDENTCELL AS "Phone 4 - Value",
FROM STUDENTS JOIN STUDENTS2 ON (STUDENTS.Stud_ID=STUDENTS2.Stud2_ID)

If Both tables are having same column name then use using

SELECT
      'Contact 1' AS "Phone 2 - Type",
      Emerg_Phone_1 AS "Phone 2 - Value",
      'Contact 2' AS "Phone 3 - Type",
      Emerg_Phone_2 AS "Phone 3 - Value",
      'Student Cell' AS "Phone 4 - Type",
      STUDENTCELL AS "Phone 4 - Value",
FROM STUDENTS JOIN STUDENTS2 using (id);

You might wanna go through Oracle JOINS and SQL JOINS

edited May 23 '17 at 11:43

Community

1
1

answered Aug 27 '15 at 14:00

Arun Palanisamy

5,281
6
28
53

I'm sorry, I'm a little confused. How do I know which columns are common? I just want the data from one (different) column in a second table, and all the other data comes from the columns in the first table. For example, the column name STUDENTCELL does not appear in the table STUDENTS at all. – Kenta Aug 28 '15 at 12:39
If you are not having common column, How do you think you can match the two tables? For eg.I assume Table 2 contains only one column `STUDENTCELL` and `10` Rows. Now If you want to take `STUDENTCELL` value from table2 for the Student Name `Kenta`(assume you have Name column in table1). How will you get the `STUDENTCELL` value for `kenta` out of 10 rows? – Arun Palanisamy Aug 28 '15 at 14:06
No, the data in the two columns are independent of each other. I am trying to generate a new table using the SELECT to pull certain columns from two different tables in my database. So for my new table, I want the rows from the column EMERG_PHONE_1 which is found in the table STUDENTS, but i would also like the rows from column STUDENTCELL which is found in table STUDENTS2. – Kenta Aug 28 '15 at 18:28
I apologize. I finally understand that there is at least one column in each table that relates all the data together. So sorry for not getting that at first, it's so simple but it just didn't click until now. I've gotten it to work finally using the following: `FROM students JOIN Sstudents2 ON students.id=students2.id2` thanks so much for the help! – Kenta Aug 28 '15 at 19:59
fine :) Glad i helped. Kindly mark it as answer if it helped you. So that it will be useful for others – Arun Palanisamy Aug 30 '15 at 05:22

score 0 · Answer 2 · answered Aug 27 '15 at 13:59

assuming that student_id is the common field in both tables, following query should do the job

SELECT
    'Contact 1' AS "Phone 2 - Type",
    s1.Emerg_Phone_1 AS "Phone 2 - Value",
    'Contact 2' AS "Phone 3 - Type",
    s1.Emerg_Phone_2 AS "Phone 3 - Value",
    'Student Cell' AS "Phone 4 - Type",
    s2.STUDENTCELL AS "Phone 4 - Value",
FROM 
    STUDENTS s1 INNER JOIN STUDENTS2 s2 ON s1.student_id=s2.student_id

Oracle - Trying to query data from two tables in the same query

2 Answers2