SQL Query that gives me all keywords, including datestored for 1 particular doc type

[Angela_Crowley]

I am looking for some help with a SQL query that would give me all keywords, including datestored (since it has time) for 1 particular doc type. Currently my user is using Generate CSV file which has worked great but it doesn't give the time that it came in. She needs a query that gives all of her keywords, date and time. Ideally date and time would be two separate columns but I can handle them in one to at least get the data out. Once I have that she also needs to know when she is using the ad hoc tasks (to approve, deny or delete which is basically changing to status keyword #741) in the workflow for this particular doc type. This doc type is a Unity form and is the only doc type in the doc type group (#122). If anyone can help, that would be great. I have this so far which does give me my datestored info but it doesn't give me any keywords (and there are 25 of them). I am thinking that maybe I need to join each keyword?

Here is what I have so far:

select * from hsi.itemdata

where itemtypegroupnum = 122

[Thomas_Reu]

if each keyword is its' own keyword in its' own table you'll need to do a left join to each keyitem table and then modify your select statement to only include the field from the keyitem table that you need.  That said, if this is a MIKG, then everything is in the same table and you will only need to join to that table and select the columns you need.  I didn't see how this was set up or perhaps I missed it...  Do you need more?

[Angela_Crowley]

Hey Tom, thanks for your response. These are not MIKG. I am still a newbie at SQL queries!

So for example, here are a couple of the keyword #'s:

Student ID	742
First Name	175
Last Name	176
Address	137
City	624
Zip Code	231
County - MN	168
MN Leg District	736

Could you maybe give me an example or two of the left join statements?

[Thomas_Reu]

w3schools can do far better than me, but this is a part sample...

select top 5 * 

  from hsi.itemdata as it

Left join hsi.keyitem### as k###

On it.itemnum = k###.itemnum

where itemtypegroupnum = 122

Note: you’ll have to do a left join for every keyword.  While repetitious this also makes for a pretty big query.  These are not the types of queries that newbies should be doing.  Any mistake along the way could create a join that returns a cartesian product that could grab all your database resources.  So, I included a top 5 to limit your results to 5, which should help protect you from yourself until you are ready.   Also, make sure to start in your test environment....

[Angela_Crowley]

Thanks fro the advice Tom! I was trying to run your query but it does not like his.itemdata.