| Author |
 Topic  |
|
|
hspatil31
Posting Yak Master
175 Posts |
 Posted - 10/26/2012 : 04:12:22
|
Dear All,
I have following query and output.
I have oc_t_item table and with oc_t_item_resource haveing the multiple records.
I need only single record from the oc_t_item_resource.
Can anyone please help me how to resolve this issue.
Ouery:
select distinct oc_t_item_resource.pk_i_id as image, oc_t_item.pk_i_id AS itemid, oc_t_item_resource.s_extension AS ext,
oc_t_item_resource.fk_i_item_id AS itemid,oc_t_item_description.s_title AS title from
oc_t_item left outer join oc_t_item_description on oc_t_item.pk_i_id = oc_t_item_description.fk_i_item_id
left outer join oc_t_item_location on oc_t_item_location.fk_i_item_id = oc_t_item.pk_i_id
left outer join oc_t_item_resource on oc_t_item_resource.fk_i_item_id = oc_t_item.pk_i_id
where oc_t_item_location.s_region= 'Matheran'
Output:
item itemid ext itemid title
182 5 jpg 5 Hotel Usha Ascot
183 5 jpg 5 Hotel Usha Ascot
184 5 jpg 5 Hotel Usha Ascot
185 5 jpg 5 Hotel Usha Ascot
186 5 jpg 5 Hotel Usha Ascot
187 5 jpg 5 Hotel Usha Ascot
Thanks and Regard's
Harish Patil |
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 10/26/2012 : 04:24:08
|
That's not the output from that query.
maybe you want to group be the columns you want a single row for and max, min or sum the others.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
hspatil31
Posting Yak Master
175 Posts |
Posted - 10/26/2012 : 04:45:45
|
Dear Freind,
Sorry insted of item that should be image in heading.
Actully I need single record means item having multiple images from the oc_t_item_resource table.
How can I get only single image (182 or 183 or 184...) from the itemid '5'.
Output:
image itemid ext itemid title
182 5 jpg 5 Hotel Usha Ascot
183 5 jpg 5 Hotel Usha Ascot
184 5 jpg 5 Hotel Usha Ascot
185 5 jpg 5 Hotel Usha Ascot
186 5 jpg 5 Hotel Usha Ascot
187 5 jpg 5 Hotel Usha Ascot
Thanks and Regard's
Harish Patil |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 10/26/2012 : 05:16:36
|
select image = max(image), itemid, ext, itemid1, title
from
(select distinct oc_t_item_resource.pk_i_id as image, oc_t_item.pk_i_id AS itemid, oc_t_item_resource.s_extension AS ext,
oc_t_item_resource.fk_i_item_id AS itemid1,oc_t_item_description.s_title AS title from
oc_t_item left outer join oc_t_item_description on oc_t_item.pk_i_id = oc_t_item_description.fk_i_item_id
left outer join oc_t_item_location on oc_t_item_location.fk_i_item_id = oc_t_item.pk_i_id
left outer join oc_t_item_resource on oc_t_item_resource.fk_i_item_id = oc_t_item.pk_i_id
where oc_t_item_location.s_region= 'Matheran'
) a
group by itemid, ext, itemid1, title
or just
select image = max(oc_t_item_resource.pk_i_id), itemid = oc_t_item.pk_i_id, ext = oc_t_item_resource.s_extension, itemid1 = oc_t_item_resource.fk_i_item_id, title = oc_t_item_description.s_title
from
(select distinct oc_t_item_resource.pk_i_id as image, oc_t_item.pk_i_id AS itemid, oc_t_item_resource.s_extension AS ext,
oc_t_item_resource.fk_i_item_id AS itemid,oc_t_item_description.s_title AS title from
oc_t_item left outer join oc_t_item_description on oc_t_item.pk_i_id = oc_t_item_description.fk_i_item_id
left outer join oc_t_item_location on oc_t_item_location.fk_i_item_id = oc_t_item.pk_i_id
left outer join oc_t_item_resource on oc_t_item_resource.fk_i_item_id = oc_t_item.pk_i_id
where oc_t_item_location.s_region= 'Matheran'
group by oc_t_item.pk_i_id, ext, oc_t_item_resource.s_extension, oc_t_item_resource.fk_i_item_id, oc_t_item_description.s_title
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
| |
Topic |
|
|
|
Need single record from multiple joined table.
