Combining two tables MYSQL

Forum|Forum|17 years ago
July 31, 2008
6 replies
766 views

Hi

I'm struggling here. This isn't as straightforward as it looks at first glance.

I've got two tables - one for assets and one for actions performed on those assets. I'm trying to create a report that shows each asset and then the most recent action of type V and also the most recent action of type E.

(actually the asset table is 6 tables linked together to transform ID codes to descriptive text)
(Note any typos below are from simplifying the actual code the page does work but very slowly - as you'd expect)

table 1: asset
assetID - primary key e.g. XX12345
assetDescription
assetLocation

table 2: assetAction
assetActionID - primary key, autoinc (using this for most recent)
assetActionAssetID - asset table's assetID
assetActionType - e.g. E, V or other things I'm not interested in at this point
assetActionResult

Currently I'm doing it a very horrible way but It got it to where I needed it to in the right timescale:

<cfquery name="assetList">SELECT * FROM asset ORDER BY assetID </cfquery>

<cfoutput query="assetList">
<tr>#assetList details#</tr>
<cfquery = "assetActionE" maxRows = "1">
SELECT * FROM assetAction
WHERE assetActionAssetID = #asset.assetID# AND assetAction = 'E'
ORDER BY assetActionID desc
</cfquery>

<tr>#assetActionE details#</tr>

<cfquery = "assetActionV" maxRows = "1">
SELECT * FROM assetAction
WHERE assetActionAssetID = #asset.assetID# AND assetAction = 'V'
ORDER BY assetActionID desc
</cfquery>

<tr>#assetActionV details#</tr>
</cfoutput>

My problem is combining the queries without creating a working table that has each asset listed for each different action. I can't find a way currently to combine the asset with just the two records from the assetAction table.

Please let me know which bits of this I haven't explained properly and I look forward to any suggested solutions

Michael

Database access

This topic has been closed for replies.

M

Mi-ul-vmijiIAuthor

Known Participant

OK,
I've gone for a change of approach but having a problem with my JOIN

I've got 2 queries I need to join - one that gets all the asset details (from many tables) and the main test results and the other that gets the visual checks (about 6 fields). (The visual and main test results are all in the same table and defined by assetAction.assetActionAction = 'E' for main test and = 'V' for visual)

The two separate queries are:

-- MAIN QUERY --

SELECT asset.*, bookingsRoom.roomID, bookingsRoom.roomRoom, assetDescription.*, assetMake.*, assetModel.*,
assetAction.*
FROM asset, assetAction, bookingsRoom, assetDescription, assetMake, assetModel
WHERE asset.assetLocation = bookingsRoom.roomID
AND asset.assetDescription = assetDescription.assetDescriptionID
AND asset.assetMake = assetMake.assetMakeID
AND asset.assetModel = assetModel.assetModelID
AND assetAction.assetActionID = asset.assetLatestPAT
ORDER BY assetID

-- VISUAL QUERY --

SELECT asset.assetID as assetIDV,
asset.assetLatestVisual,
assetAction.assetPATResult as assetPATResultV,
assetAction.assetActionAction as assetActionActionV,
assetAction.assetPATTester as assetPATTestetV,
assetAction.assetPATDate as assetPATDateV
FROM asset, assetAction
WHERE assetAction.assetActionID = asset.assetLatestVisual
ORDER BY assetID

My attempts at JOINing these give the following code however I'm getting 2025 results instead of 45 (reason: 45*45 = 2025 so I'm getting each row of one table against the additional fields of every row of the 2nd table)
I suspect I need to JOIN ( ... ) AS VISUAL ON ???

Any suggestions?

Many thanks
Michael

-- COMBINED CODE --

SELECT VISUAL.*, asset.*, bookingsRoom.roomID, bookingsRoom.roomRoom, assetDescription.*, assetMake.*, assetModel.*,
assetAction.*
FROM asset, assetAction, bookingsRoom, assetDescription, assetMake, assetModel
JOIN (
SELECT asset.assetID as assetIDV,
asset.assetLatestVisual,
assetAction.assetPATResult as assetPATResultV,
assetAction.assetActionAction as assetActionActionV,
assetAction.assetPATTester as assetPATTestetV,
assetAction.assetPATDate as assetPATDateV
FROM asset, assetAction
WHERE assetAction.assetActionID = asset.assetLatestVisual
AND asset.assetID = assetAction.assetActionAssetID
ORDER BY assetID
) as VISUAL

WHERE asset.assetLocation = bookingsRoom.roomID
AND asset.assetID = assetAction.assetActionAssetID
AND asset.assetDescription = assetDescription.assetDescriptionID
AND asset.assetMake = assetMake.assetMakeID
AND asset.assetModel = assetModel.assetModelID
AND assetAction.assetActionID = asset.assetLatestPAT
ORDER BY assetID

N

Newsgroup_User

Inspiring

Mi-ul wrote:
> I need the 2 queries on assetAction to be in the same set of results

Have you read the MySQL manual? There is a page in it about getting the
groupwise maximum. Have you tried that?

Jochem

--
Jochem van Dieten
Adobe Community Expert for ColdFusion

M

Mi-ul-vmijiIAuthor

Known Participant

Phil,

I need the 2 queries on assetAction to be in the same set of results

P

paross1

Participating Frequently

Something like this?

SELECT a.assetID,
a.assetDescription,
a.assetLocation,
aa.assetActionID,
aa.assetActionType,
aa.assetActionResult
FROM asset a
INNER JOIN assetAction aa ON aa.assetActionAssetID = a.assetID
WHERE aa.assetActionType = 'E'
AND aa.assetActionID = (SELECT MAX(x.assetActionID)
FROM assetAction x
WHERE x.assetActionAssetID = a.assetID
AND x.assetActionType = aa.assetActionType)
ORDER BY a.assetID

SELECT a.assetID,
a.assetDescription,
a.assetLocation,
aa.assetActionID,
aa.assetActionType,
aa.assetActionResult
FROM asset a
INNER JOIN assetAction aa ON aa.assetActionAssetID = a.assetID
WHERE aa.assetActionType = 'V'
AND aa.assetActionID = (SELECT MAX(x.assetActionID)
FROM assetAction x
WHERE x.assetActionAssetID = a.assetID
AND x.assetActionType = aa.assetActionType)
ORDER BY a.assetID

Phil

P

paross1

Participating Frequently

How about combining them, order by aa.assetActionType, then group by that field in the output?

SELECT a.assetID,
a.assetDescription,
a.assetLocation,
aa.assetActionID,
aa.assetActionType,
aa.assetActionResult
FROM asset a
INNER JOIN assetAction aa ON aa.assetActionAssetID = a.assetID
WHERE aa.assetActionType IN ('E', 'V')
AND aa.assetActionID = (SELECT MAX(x.assetActionID)
FROM assetAction x
WHERE x.assetActionAssetID = a.assetID
AND x.assetActionType = aa.assetActionType)
ORDER BY aa.assetActionType , a.assetID

Phil

M

Mi-ul-vmijiIAuthor

Known Participant

The code below shows everything _almost_ working. Note this is the actual code rather than the simplified version.

However my problem now is the report layout (but linked to the db problem). I'm after a table that looks like

Asset ID Description Location Make
Visual date retest
Full_test date retest

but I can only get details of the 2nd or 3rd line Or I get

Asset ID Description Location Make
Visual date retest
Asset ID Description Location Make
Full_test date retest

which isn't quite as useful as I'd like.

Any suggestions before I revert to plan F which is to add the latest actionIDs to the asset table and grab them from that as needed

Michael

M

Mi-ul-vmijiIAuthor

Known Participant

Update:

To clarify - I need to show the asset details then the latest 'E' action (i.e. has the highest assetActionID value where asset = E and assetID is #asset.assetID#) then the latest 'V' action

D

Dan_Bracuk

Inspiring

Instead of a working table, use a subquery. Something like this.

select somefields
from sometables
join (select somefields, max(somedatefield) maxdate
from sometables
where whatever
group by somefields ) xyz on somedatefield = maxdate
where whatever

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded