469,090 Members | 1,310 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

Join same column with different data.

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.

Expand|Select|Wrap|Line Numbers
  1. SELECT distinct(`Tickets`.`EffectiveId`), `Tickets`.`Subject`, `Tickets`.`Created`, `Transactions`.`OldValue`,
  2. `Transactions`.`NewValue`, `Transactions`.`Created` as `Tcreated`,
  3. `ObjectCustomFieldValues`.`Content` as `sev`,
  4. `ObjectCustomFieldValues`.`Content` as `tt`
  5. from `Tickets`
  6. Left join (`Transactions`, `ObjectCustomFieldValues`)
  7. on (`Tickets`.`EffectiveId` = `Transactions`.`ObjectId`
  8. and `ObjectCustomFieldValues`.`ObjectId` = `Tickets`.`EffectiveId`)
  9. where `Tickets`.`id` = `Tickets`.`EffectiveId`
  10. and `Tickets`.`Created` >='2009-08-01 07:00:00'
  11. and `Tickets`.`Created` <= '2009-08-31 23:59:59'
  12. and (`Tickets`.`Status` = 'resolved'
  13. or `Tickets`.`Status` = 'closed')
  14. and `Transactions`.`Type` = 'Status'
  15. and (`ObjectCustomFieldValues`.`CustomField` = '32' or `ObjectCustomFieldValues`.`CustomField`= '24')
  16. and `ObjectCustomFieldValues`.`Disabled` = '0'
  17. order by `Tcreated`, `Tickets`.`EffectiveId` asc;
  18.  
That be the query I am using, at the moment. the only place where I am having problems is on the ObjectCustomFieldValues 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
Nov 3 '09 #1
3 2657
wizardry
201 100+
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?
Nov 4 '09 #2
`ObjectCustomFieldValues`.`Content` as `sev`,
`ObjectCustomFieldValues`.`Content` 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.
Nov 4 '09 #3
wizardry
201 100+
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
Nov 4 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Dave | last post: by
reply views Thread by Preston Landers | last post: by
6 posts views Thread by Christopher Harrison | last post: by
4 posts views Thread by Anthony Robinson | last post: by
6 posts views Thread by PW | last post: by
7 posts views Thread by jason.langdale | last post: by
3 posts views Thread by don | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.