0

We're developing a PHP page to create some reports. We're using data from a database that is "populated" by a third-party application.

We need to execute a query to select "tickets" between a period and to select other data from another table.

Example:

//Select tickets closed between dates
SELECT *
FROM ticsummary
WHERE resolution_date between #07/04/2013# AND #08/04/2013#;

//Result
Ticket  Resolution_date
61968   07/04/2013

This (above) is working great. However we need to add a content that is in another table named ticFieldVals. The content from this table is:

ticId   fieldId    intVal  numVal  strVal
61968   1          4       NULL    NULL
61968   2          3       NULL    NULL
61968   3          2       NULL    NULL
61968   100        2       NULL    NULL
61968   103        NULL    300.000 NULL
61968   85253767   0       NULL    NULL
61968   73913495   1       NULL    NULL
61968   23260488   NULL    NULL    NULL
61968   83015458   1       NULL    NULL
61968   33742201   1       NULL    NULL
61968   57589628   1       NULL    NULL
61968   91660570   NULL    NULL    NULL

So I need to execute a "join", to have both of these tables in just one table. However the content of ticFieldVals there is a lot of rows, and I need to put all together in same row.

I'm sure if you can totally understand, but any help is greatly appreciated.

Tony
  • 9,672
  • 3
  • 47
  • 75
user2840263
  • 1
  • 1
  • What database are you using? If Oracle, this should be possible as explained in detail in the following article: http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php – Przemyslaw Kruglej Oct 02 '13 at 20:59
  • Some of your rows have 6 columns while others have 5 ?? – Michael Berkowski Oct 02 '13 at 21:00
  • Sorry, I'm using SQL Server 2008. – user2840263 Oct 02 '13 at 21:03
  • Actually, occurs an error when I post. Now its the code below, in question. Thank you Michael – user2840263 Oct 02 '13 at 21:05
  • There's a lot of information in the `ticFieldVals` table to concatenate in to a single row. Can you describe/show what you expect the output to look like? – Tony Oct 02 '13 at 21:18
  • possible duplicate of [Is there a way to create a SQL Server function to “join” multiple rows from a subquery into a single delimited field?](http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-sql-server-function-to-join-multiple-rows-from-a-su) – Tony Oct 02 '13 at 21:20

1 Answers1

0

Did you try:

//Select tickets closed between dates
SELECT *
FROM ticsummary t join ticFieldVals tt on (t.Ticket=tt.ticId)
WHERE resolution_date between #07/04/2013# AND #08/04/2013#;
Cedric Simon
  • 4,571
  • 4
  • 40
  • 52
  • You missed the part of the question where they said "the content of `ticFieldVals` there is a lot of rows, and I need to put all together in same row". They only want one row per ticket, with all the `ticFieldVals` concatenated in to a single field. – Tony Oct 02 '13 at 21:16
  • Hello Cedric, when I execute this command I got more than one row, but I need just in one row, to put it on PHP (html) table... – user2840263 Oct 02 '13 at 21:22
  • Sorry, my misundertanding of the question. – Cedric Simon Oct 02 '13 at 21:38