Inner Join SQL Syntax

Question

I've never done an inner join SQL statement before, so I don't even know if this is the right thing to use, but here's my situation.

Table 1 Columns: id, course_id, unit, lesson Table 2 Columns: id, course_id

Ultimately, I want to count the number of id's in each unit in Table 1 that are also in Table 2.

So, even though it doesn't work, maybe something like....

$sql = "SELECT table1.unit, COUNT( id ) as count, table2.id, FROM table1, table2, WHERE course_id=$im_course_id GROUP BY unit";

I'm sure the syntax of what I'm wanting to do is a complete fail. Any ideas on fixing it?

Pupa Rebbe · Accepted Answer · 2014-06-25 17:02:16Z

1

SELECT unit, COUNT( t1.id ) as count
FROM table1 as t1 inner JOIN table2 as t2 
  ON t1.id = t2.id
GROUP BY unit

hope this helps.

answered Jun 25, 2014 at 17:02

Pupa Rebbe

5282 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jonny · Accepted Answer · 2014-06-25 15:59:16Z

0

If I understand what you want (maybe you could post an example input and output?):

SELECT unit, COUNT( id ) as count
FROM table1 as t1 JOIN table2 as t2 
  ON t1.id = t2.id
GROUP BY unit

answered Jun 25, 2014 at 15:59

Jonny

2,5191 gold badge25 silver badges43 bronze badges

2 Comments

gtilflm Over a year ago

This isn't working. MySQL says "#1052 - Column 'id' in field list is ambiguous". Thoughts?

user2366842 Over a year ago

it's not sure if you mean id from table1 or table2, change COUNT (id) to COUNT( t1.id ) or COUNT (t2.id)

Jenn · Accepted Answer · 2014-06-25 17:03:59Z

Okay, so there are a few things going on here. First off, commas as joins are deprecated so they may not even be supported (depending on what you are using). You should probably switch to explicitly writing inner join

Now, whenever you have any sort of join, you also need on. You need to tell sql how it should match these two tables up. The on should come right after the join, like this:

Select *
From    table1 inner join table2
    on table1.id = table2.id
    and table1.name = table2.name

You can join on as many things as you need by using and. This means that if the primary key of one table is several columns, you can easily create a one-to-one match between tables.

Lastly, you may be having issues because of other general syntax errors in your query. A comma is used to separate different pieces of information. So in your query,

SELECT table1.unit, COUNT( id ) as count, table2.id, FROM ...

The comma at the end of the select shouldn't be there. Instead this should read

SELECT table1.unit, COUNT( id ) as count, table2.id FROM ...

This is subtle, but the sql query cannot run with the extra comma.

Another issue is with the COUNT( id ) that you have. Sql doesn't know which id to count since table1 and table2 both have ids. So, you should use either count(table1.id) or count(table2.id)

Collectives™ on Stack Overflow

Inner Join SQL Syntax

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related