SQL query join

[Everton1878]

Hi,

I'm trying to do a SQL query in the Database Direct with a join but I'm having a bit of a problem

I want to return all data in h_itsm_requests that matches records in h_itsm_questions where h_question_id = 'citrixserver' but I'm not getting any data

It's either an issue with the joining or because I have criteria on both tables

Quote

SELECT h_pk_reference, h_summary, h_description, h_fk_user_name, h_status, h_dateresolved, h_dateresolved, h_ownername, h_fk_team_name, h_closure_category, h_resolution, h_answer FROM h_itsm_requests INNER JOIN h_itsm_questions on h_pk_reference = h_entity_ref WHERE h_question_id = 'citrixserver' AND h_closure_category like 'Citrix%' AND (h_dateresolved >= LAST_DAY(NOW() - INTERVAL 2 MONTH) + INTERVAL 1 DAY AND h_dateresolved < LAST_DAY(NOW() - INTERVAL 1 MONTH))

This next one returns 52 records but there should be 134, it's only returning records where the criteria on both tables is matched

I've tried left join and right join but it hasn't made a difference

Quote

SELECT h_pk_reference, h_summary, h_description, h_fk_user_name, h_status, h_dateresolved, h_dateresolved, h_ownername, h_fk_team_name, h_closure_category, h_resolution, h_answer FROM h_itsm_requests inner join h_itsm_questions on h_pk_reference = h_entity_ref WHERE h_question_id = 'citrixserver' AND h_closure_category like 'Citrix%' AND (h_dateresolved >= LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY AND h_dateresolved < LAST_DAY(NOW()))

In need to return everything that matches the following query

Quote

SELECT h_pk_reference, h_summary, h_description, h_fk_user_name, h_status, h_dateresolved, h_dateresolved, h_ownername, h_fk_team_name, h_closure_category, h_resolution FROM h_itsm_requests WHERE h_closure_category like 'Citrix%' AND (h_dateresolved >= LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY AND h_dateresolved < LAST_DAY(NOW()))

and return h_answer from h_itsm_questions where h_question_id = 'citrixserver'

 

Can anyone help?

This level of SQL is a bit beyond me

[Guest]

Hi @Everton1878

Just having a look at this - and I'm just trying to establish how you are looking for this to work::

So lets say that the query:

Quote

SELECT h_pk_reference, h_summary, h_description, h_fk_user_name, h_status, h_dateresolved, h_dateresolved, h_ownername, h_fk_team_name, h_closure_category, h_resolution FROM h_itsm_requests WHERE h_closure_category like 'Citrix%' AND (h_dateresolved >= LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY AND h_dateresolved < LAST_DAY(NOW()))

Returns 100 results - but 90 of these have an associated h_answer in the questions table that DOES NOT match "citrixserver" - how many results would you like to return?

All 100 with whatever the h_answer is for all of them, or the 10 that do match?

Kind Regards

Bob

[Everton1878]

Hi @Bob Dickinson

erm....

I want all 100 results

The problem is that there might be multiple questions per call but we're only interested in the h_answer field that matches "citrixserver" otherwise we will get multiple lines per call reference

I've attached a spreadsheet which I hope might make things a bit easier to understand

The first tab contains the results of the query on h_itsm_requests

The second tab contains the results of the query on h_itsm_questions where h_question_id = "citrixserver"

The third tab shows that there might be multiple answers per call reference

I just want to link the data in the first tab with the data in the second tab showing all of the data in the first tab and the data from the second tab where the reference number matches

 

I'm not sure if it can be done in one single query, it might be that I have to do 2 queries and maybe a lookup in Excel

In SQL you might create a view for h_itsm_questions where h_question_id = "citrixserver" and link that to your main query

I hope the spreadsheet helps it make more sense

 

 

 

 

SQL join problem.xlsx

[Guest]

Hi @Everton1878

Ok it took a bit of time and research, but I think this can be achieved using a sub-select statement to in effect give you a subset of results from the h_itsm_questions table to perform the join upon (i.e. all of the entries in there that have a h_question_id = 'citrixserver'

So could you please try the following and see if this works for you?:

Quote

SELECT DISTINCT a.h_pk_reference, a.h_summary, a.h_description, a.h_fk_user_name, a.h_status, a.h_dateresolved,
a.h_dateresolved, a.h_ownername, a.h_fk_team_name, a.h_closure_category, a.h_resolution, b.h_answer
FROM (select h_entity_ref, h_question_id, h_answer from h_itsm_questions where h_question_id = 'citrixserver') as b
RIGHT JOIN h_itsm_requests as a
on a.h_pk_reference = b.h_entity_ref 
WHERE a.h_closure_category like 'Citrix%' AND (a.h_dateresolved >= LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY AND a.h_dateresolved < LAST_DAY(NOW()))
 

Let me know if this works, I'll save it for future reference!

Kind Regards

Bob

[Everton1878]

Hi @Bob Dickinson, that looks to give me just what I was after , much appreciated!