MySQL Inner Join, selecting from multiple tables

Question

I'm really struggling to get my head around this. I am trying to run a SELECT query from multiple tables.

This is what I have so far that doesn't work;

SELECT jira_issues.*, session_set.* FROM jira_issues, session_set
INNER JOIN reports on jira_issues.report_id = reports.id 
    WHERE jira_issues.report_id = 648

I have other tables (session_set, report_device) which has a ReportID and report_id column respectively.

I have a report table which has a Primary Key id. In the other tables the report.id key is linked with foreign keys.

Ultimately what I am trying to achieve is this: I have an entry in the reports table with an id of 648. In the other tables (jira_issues, report_device, session_set), I also have entries which has a foreign key linked to the report id in the report table.

I want to run one SELECT Query to query the tables (jira_issues, report_device and session_set) and get all the data from them based on the report.id.

Thanks!

As a best practice: Use one type of join - the ANSI style (i.e.,JOIN foo ON .. You are using both the ANSI style and the theta style (i.e., the comma separated tables in the FROM clause). — AgRizzo
– AgRizzo, Commented Feb 11, 2014 at 12:15

Nick · Accepted Answer · 2014-02-11 12:19:27Z

3

What about this:

SELECT * FROM jira_issues ji
    LEFT JOIN session_set ss ON ji.report_id = ss.ReportID
    LEFT JOIN report_device rd ON rd.report_id = ji.report_id
WHERE ji.report_id = 648;

answered Feb 11, 2014 at 12:19

Nick

721 silver badge2 bronze badges

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

5 Comments

Jake Evans Over a year ago

Ah super! What if I wanted to limit the columns from each of the tables? For example, I only want issue_id and issue_key from the jira_issues table and the device_name from the report_device table. Thanks!

Kickstart Over a year ago

Just specify the column name in the SELECT rather than using SELECT *. For exaple SELECT ji.issue_id, ji.issue_key .

Jake Evans Over a year ago

Thanks, one more thing! What if I wanted to select the data from the Reports table too?

Kickstart Over a year ago

You can LEFT JOIN on another table easily enough

Nick Over a year ago

Instead of SELECT *, just use SELECT ji.field1, rd.field2 to select field1 from table jira_issues and field2 from table report_device.

Gordon Linoff · Accepted Answer · 2014-02-11 12:17:56Z

0

Just say "no" to commas in the from clause. Always use explicit join syntax:

SELECT ji.*, session_set.*
FROM jira_issues ji inner join
     reports r
     on ji.report_id = r.id  inner join
     session_set ss
     on ss.ReportId = r.report_id
WHERE ji.report_id = 648;

If some of the tables might have no corresponding rows, you might want left outer join instead of inner join.

answered Feb 11, 2014 at 12:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Jwalin Shah · Accepted Answer · 2014-02-11 12:20:28Z

0

Kindly try this out. You may get syntax error.

SELECT a., b. FROM jira_issues a, session_set b, reports c Where a.report_id = c.id and b.report_id = c.id AND a.report_id = 648

answered Feb 11, 2014 at 12:20

Jwalin Shah

2,5311 gold badge19 silver badges22 bronze badges

Collectives™ on Stack Overflow

MySQL Inner Join, selecting from multiple tables

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related