Join same column with different data.

[tc3driver]

Hello Folks,

I have an interesting problem, let me start off with, this database is not of my design, is an absolute mess, and there is nothing I can do about it.

SELECT distinct(`Tickets`.`EffectiveId`), `Tickets`.`Subject`, `Tickets`.`Created`, `Transactions`.`OldValue`,
`Transactions`.`NewValue`, `Transactions`.`Created` as `Tcreated`,
`ObjectCustomFieldValues`.`Content` as `sev`,
`ObjectCustomFieldValues`.`Content` as `tt`
from `Tickets`
Left join (`Transactions`, `ObjectCustomFieldValues`)
on (`Tickets`.`EffectiveId` = `Transactions`.`ObjectId`
and `ObjectCustomFieldValues`.`ObjectId` = `Tickets`.`EffectiveId`)
where `Tickets`.`id` = `Tickets`.`EffectiveId`
and `Tickets`.`Created` >='2009-08-01 07:00:00'
and `Tickets`.`Created` <= '2009-08-31 23:59:59'
and (`Tickets`.`Status` = 'resolved'
or `Tickets`.`Status` = 'closed')
and `Transactions`.`Type` = 'Status'
and (`ObjectCustomFieldValues`.`CustomField` = '32' or `ObjectCustomFieldValues`.`CustomField`= '24')
and `ObjectCustomFieldValues`.`Disabled` = '0'
order by `Tcreated`, `Tickets`.`EffectiveId` asc;

That be the query I am using, at the moment. the only place where I am having problems is on the ObjectCustomFie ldValues table. In that table there are 2 identifiers that I can use to pull data, the ObjectId and the CustomField, The CustomField is for equating a line with an actual custom field ID (elsewhere in the database), and the Object ID is a global ID through out the database. I need two different pieces of information from this table in a single call, and I am stumped.

I would like to see the sev and tt as separate columns with the data for their CustomField id in each row returned.

If I cannot figure this out, I'll just start writing to a more useful database, where the information for each field I need in in a separate column or table etc... I am just hoping I can get some help before it gets to this point.

Thanks,
TC

[wizardry]

i guess im not understanding your question. you want the sev and tt in two sepearte columns in the query?

but your query is pulling them as 2 seperate coulmns. with each its own custom field.

can you elaborate more on what your trying to do?

[tc3driver]

`ObjectCustomFi eldValues`.`Con tent` as `sev`,
`ObjectCustomFi eldValues`.`Con tent` as `tt`


So the return value of this query looks somewhat like this:

EffectiveId, 'Subject', Created, OldValue, NewValue, Tcreated, sev, tt
33544, 'Flapping.', '2009-08-02 22:19:18', 'new', 'open', '2009-08-02 22:34:35', 'Problem', 'Problem'
33544, 'Flapping.', '2009-08-02 22:19:18', 'new', 'open', '2009-08-02 22:34:35', 'Severity 3', 'Severity 3'

What I would like to see is:

33544, 'Flapping.', '2009-08-02 22:19:18', 'new', 'open', '2009-08-02 22:34:35', 'Severity 3', Problem


As you can see they are pulled from the same column in the same table.

but that column contains different information for each custom field, so the the ones with CustomField of 24 go into tt, and CustomField 32 goes into sev. Basically so they end up in thier own columns in the query return.

Yes I know it is confusing, like I said, I didn't write the database, which is good for single lookups, but poor for reporting on multiple entries.

[wizardry]

well with the current query you have 2 parameters that need to be meet for sev and tt. you could create a sub select query to establish the desired query results for the one entity. but doing what you looking for in one query for two of the entitys two pull the data back that matches where clause. your going to have dup entries as you do now. thats why you would need to create a sub query for the desired entity.

i would say change your join type but left join is the same as inner join.

i hope this could help.

here is a link for sub selects in mysql:
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html