MySQL - JOIN MULTIPLE TABLES

[AndieB]

Hello all Gurus!

I need help with a QUERY SYNTAX for MySQL.

I have the following TABLES in my database:

[b]incident[/b]
[b]iinfo[/b]
[b]iplatform[/b]
[b]iserver[/b]
[b]icomponent[/b]

In each of the TABLES there is a COLUMN with name name 'id'. This is the COLUMN that "logically" is the reference point. Now, I would like to get a query that fetches ALL the columns from ALL the tables above based on the existens 'id' in the "main" table which is the [b]incident[/b] TABLE.

How do I write such a QUERY SYNTAX?

I appreciate any kind of help!

Best regards,

Andreas

[fenway]

I'm not sure I understand... they all have FKs to the main "incident" table?

Then maybe:

[code]
SELECT incident.*, iinfo.*, iplatform.*, iserver.*, icomponent.*
FROM incident
LEFT JOIN iinfo ON iinfo.incident_id = incident.id
LEFT JOIN iplatform ON iplatform.incident_id = incident.id
LEFT JOIN iserver ON iserver.incident_id = incident.id
LEFT JOIN icomponent ON icomponent.incident_id = incident.id
[/code]

[AndieB]

Thank you for this!

The only thing I had to correct was the following:

[b]From[/b]
LEFT JOIN iinfo ON iinfo.incident_id = incident.id
[b]to[/b]
LEFT JOIN iinfo on iinfo.id = incident.id

The only thing I still want to solve, is that the COLUMN 'id' is now shown from every TABLE in my RESULT. Since I already have the 'id' from the TABLE incident, I don't need it any further in the rest of the RESULT. Is this possible?

Again, thank you so much for your time and effort!

--Andreas


[quote author=fenway link=topic=113320.msg460734#msg460734 date=1162323317]
I'm not sure I understand... they all have FKs to the main "incident" table?

Then maybe:

[code]
SELECT incident.*, iinfo.*, iplatform.*, iserver.*, icomponent.*
FROM incident
LEFT JOIN iinfo ON iinfo.incident_id = incident.id
LEFT JOIN iplatform ON iplatform.incident_id = incident.id
LEFT JOIN iserver ON iserver.incident_id = incident.id
LEFT JOIN icomponent ON icomponent.incident_id = incident.id
[/code]
[/quote]

[fenway]

Well, the "solution" is just to ask for the columns you want.. there's no way to exclude columns per se.

[AndieB]

[quote author=fenway link=topic=113320.msg461338#msg461338 date=1162413678]
Well, the "solution" is just to ask for the columns you want.. there's no way to exclude columns per se.
[/quote]

Ok I understand. I've done that now.
Now my QUERY looks like this:

[code]SELECT incident.*,
iplatform.platform,
iserver.server,
icomponent.component,
iinfo.cust_impact,
iinfo.followup,
iinfo.vinstsr,
iinfo.incidentreport,
iinfo.gsp,
ihistory.descrip
FROM incident
LEFT JOIN iinfo ON iinfo.id = incident.id
LEFT JOIN iplatform ON iplatform.id = incident.id
LEFT JOIN iserver ON iserver.id = incident.id
LEFT JOIN icomponent ON icomponent.id = incident.id
LEFT JOIN ihistory on ihistory.id = incident.id
ORDER BY incident.idate ASC[/code]

In the TABLE ihistory the design looks like this:
[code]
COLUMN | DATATYPE | NULL | STANDARD VALUE
-----------------------------------------------
id | int(11) | NO | 0

datentime | datetime | NO | 0000-00-00 00:00:00

descrip | longtext | Yes | NULL

[/code]

The PRIMARY KEYS for this TABLE are: id AND datentime

When I run the QUERY written above, the list will display the same id twice, because there are [b]two[/b] rows in the ihistory table. If I only want to show [b]one[/b] row, since there may not be duplicates in the incident TABLE, and the FIRST row in the ihistory TABLE, how does my QUERY need to be written?

Thank you again for your time and effort helping me out!

[b]Andreas[/b]

[fenway]

Well, there a few ways: you could simply use a GROUP BY clause, depending on how much you care about which one you get, or you could run a subquery and pull just one back.

[AndieB]

Hmm... OK, I understand what you mean. Then it is to the practical... meaning, writing the QUERY...  ;)

How would it look with a SUBQUERY in my existing example?

Thank you again for your time and effort!

--[b]Andreas[/b]

[fenway]

That depends.. what if there are multiple records returned from the other tables?

[AndieB]

Hmmmm... *thinking*


Well, there are ONE unique entry from the incident TABLE. The only table that can have MORE rows one ID is the ihistory table. All other tables can only have ONE row.

ie. incident.id = 1, means that it can only be  ONE *.id = 1 in the other TABLES, excluding the ihistory table. As you could see the PRIMARY KEYS for the ihistory TABLE are id & datentime.

Hmm, do I explain good or am I totally gone in explanation??

Best regards,

[b]Andreas[/b]

[fenway]

I'll I'm saying is that nothing about the structure enforces that, so it really depends what you're looking for.

[AndieB]

Okey...

Well, I solved it by making a second QUERY while fetching and displaying the rows... so it is kind of a subquery but not created in one QUERY statement...