I have been trying to use application code to loop a tesult set with absolutely no luck whatsoever. I would like to know if it is possible using some fancy artsy fartsy sql to get this working.
The code below gives me my entire result set which is fine however I need one row for location_name, addr1, and city. All of the other attributes will have 1 and many rows associated with the location_name, city, etc.
Code:
SELECT
t1.location_name
, t1.addr1
, t1.city
, t2.name
, t2.email
, t3.w_name
, t3.r_type
, t3.i_type
, t3.disposition
, t3.report
FROM
location AS t1
Inner
Join email AS t2 ON t1.location_id = t2.location_id AND t1.customer_id = t2.customer_id
Left Outer
Join report AS t3 ON t1.customer_id = t3.customer_id AND t1.location_id = t3.location_id
WHERE t3.stamp >= date_sub(now(), interval 1 day) AND t1.customer_id = '1' AND t2.location_id = '66'
My desired first row output would look like:
Code:
location_name addr1 city
somecustomer 1234.. Los Angeles
And the next row:
Code:
name email w_name r_type i_type disposition report
tom a@d.. Jim W gold band A 1
jane w@f.. Pete silver band A 0
And so on..
Can this be acomplished?
dBforums
>
Query help
08-18-08, 08:14
Linear Mode
