Need single record from multiple joined table.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need single record from multiple joined table.

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2012-10-26 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-26 : 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

182 Posts

Posted - 2012-10-26 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-26 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources