Simple SQL Select from 2 Tables (What Is a Join)

Simple SQL Select from 2 Tables (What is a Join?)

You do, in fact, want to JOIN the two tables:

SELECT * FROM
    TABLE_USERS LEFT JOIN TABLE_GROUPS 
    ON TABLE_USERS.group = TABLE_GROUPS.id

The trick of joining tables is to find the values that must match in the two tables, and use the on to tell SQL to match them. This table has a ID column to let you do that = you will join the table, ON, and then list the values that need to be equal.

If you do not want all of the columns in both tables, you can simply list only the columns you need in your final query. This means that instead of Select *, you list the columns you want. As shown below, if a column appears with the same name in both tables, you need to prepend the table name, so that SQL know which value you want.

SELECT TABLE_USERS.ID, Username, Groupname 
  FROM TABLE_USERS 
     LEFT JOIN TABLE_GROUPS 
     ON TABLE_USERS.group = TABLE_GROUPS.id

select 2 columns from 2 tables using a join

You want a left join, something like:

select t1.bookname, t2.noOfPages
from table1 t1 left join
     table2 t2
     on t1.bookname = t2.bookname;

You may get more results than in table1 if there are duplicates in table2. You can find these by doing:

select t2.bookname
from table2 t2
group by t2.bookname
having count(*) > 1;

Combining values from multiple tables using join clause with multiple ON

This approach uses UNION ALL to combine the letter named tables (tbla, tblb, tblc, tbld) into a CTE, common table expression. The combined table is then summarized by id, [desc] and crosstabulated (or pivoted) across the login columns. The pivoted result is then LEFT JOIN'ed to the master_tbl. Something like this.

with
tbl_cte(tbl, id, [login], [desc]) as (
    select 'A', * from tbla
    union all
    select 'B', * from tblb
    union all
    select 'C', * from tblc
    union all
    select 'D', * from tblc),
pvt_cte(id, tbla_login, tblb_login, tblc_login, tbld_login, [desc]) as (
    select id, 
           max(case when tbl='A' then [login] else null end) as tbla_login,
           max(case when tbl='B' then [login] else null end) as tblb_login,
           max(case when tbl='C' then [login] else null end) as tblc_login,
           max(case when tbl='D' then [login] else null end) as tbld_login,
           [desc] 
    from tbl_cte
    group by id, [desc])
select mt.id, [name], country, [status], pc.tbla_login, 
       pc.tblb_login, pc.tblc_login, pc.tbld_login, pc.[desc]
from master_tbl mt
     left join pvt_cte pc on mt.id=pc.id;

Join 2 tables SQL query

No need of looping, you can do a JOIN between the tables like

select t2.*
from Table_2 t2 join Table_1 t1 on t2.table_1_id = t1.id
where t1.status = 1
and date(t2.`time`) = date(now() - interval 1 day);

Join 2 table with different where clause

You can join it like:

$sql="SELECT admin_store_exam.A_QNum
 , admin_store_exam.math_ques
 , admin_store_exam.math_a
 , admin_store_exam.math_b
 , admin_store_exam.math_c
 , admin_store_exam.math_d
 , admin_store_exam.A_Ans
 , CONCAT(S_Ans,S_Noans) AS S_Ans
  FROM admin_store_exam 
 INNER 
  JOIN student_ans_history 
    ON admin_store_exam.exam_name = student_ans_history.exam_name 
        AND admin_store_exam.A_QNum = student_ans_history.S_QNum
 WHERE admin_store_exam.exam_name = '$en' 
   AND student_ans_history.S_ID = '$session_contact'
    GROUP BY admin_store_exam.exam_name, admin_store_exam.A_QNum";

Hope this works!!!

Join two tables... without JOIN

Standard way to implement FULL OUTER JOIN when only implicit joins are supported.

select t1.id t1id, t2.id t2id
from t1, t2 where t1.id = t2.id

union all

select id, null from t1
where not exists (select 1 from t2 where t2.id = t1.id)

union all

select null, id from t2
where not exists (select 1 from t1 where t1.id = t2.id)

order by coalesce(t1id, t2id)

The first SELECT produces the INNER JOIN part of the result.

The second SELECT adds the additional LEFT OUTER JOIN rows to the result.

The third SELECT adds the additional RIGHT OUTER JOIN rows to the result.

All together, a FULL OUTER JOIN is performed!

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ec154ad243efdff2162816205fdd42b5

SQL Query Join 2 tables

Then you have two tables :

// One record per student / sport association
student_sport
    StudentID 
    SportID

// A match is only for one sport (warning to your plural) no?
matches
    SportID
    MacthID

You want: For one student all sport already played in a match

SELECT DISTINCT StudentID, student_sport.SportID
FROM student_sport, matches

WHERE 
      -- Select the appropriate player
      student_sport.StudentID = @StudentID    
      -- Search all sport played in a match and plays by the student 
      -- (common values sportid btw student_sport & matches)
  AND student_sport.SportID = matches.SportID 

or use this other syntax (JOIN IN) (it makes complex queries easier to understand, so it's good to learn)

SELECT DISTINCT StudentID, student_sport.SportID
FROM student_sport
-- Search all sport played in a match and plays by the student 
-- (common values sportid btw student_sport & matches)
INNER JOIN matches on student_sport.SportID = matches.SportID
WHERE 
      -- Select the appropriate player
      student_sport.StudentID = @StudentID    

ps: Includes Jan Hudec Coments, tx for it

Inner join 2 tables into one

Like Gordon said, it's not recommended to mix implicit and explicit joins in the same query. Here is how your query is written with implicit joins:

SELECT *
FROM [overflow$], [stack1$], [stack2$]
WHERE [stack1$].[iddate] = [overflow$].[iddate]
  AND [stack1$].[idbusiness] = [overflow$].[idbusiness]
  AND [stack2$].[iddate] = [overflow$].[iddate]
  AND [stack2$].[idbusiness] = [overflow$].[idbusiness]

For the sake of completeness, here is how your query is written with explicit joins (as in Gordon's answer, but using your naming style):

SELECT *
FROM ([overflow$]
      INNER JOIN [stack1$]
              ON [stack1$].[iddate] = [overflow$].[iddate]
             AND [stack1$].[idbusiness] = [overflow$].[idbusiness])
INNER JOIN [stack2$]
        ON [stack2$].[iddate] = [overflow$].[iddate]
       AND [stack2$].[idbusiness] = [overflow$].[idbusiness]

It is generally recommended to use explicit joins. Sadly, the Jet syntax requires parentheses for joins, which makes it harder to write & read.

If those conditions are correct, the two queries above should be identical and give you the same results. However, some database have issues/bugs with the implicit joins, so the results (mainly performance) may vary. I don't know about Vertica, but for recent versions of SQL Server, there should be no difference.

If you're not getting records from the above queries, then the conditions are probably wrong. I suspect that you actually want the results of two queries combined with UNION:

SELECT *
FROM [overflow$]
INNER JOIN [stack1$]
        ON [stack1$].[iddate] = [overflow$].[iddate]
       AND [stack1$].[idbusiness] = [overflow$].[idbusiness]
UNION ALL
SELECT *
FROM [overflow$]
INNER JOIN [stack2$]
        ON [stack2$].[iddate] = [overflow$].[iddate]
       AND [stack2$].[idbusiness] = [overflow$].[idbusiness]

---

Related Topics