SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need single record from multiple joined table.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hspatil31
Posting Yak Master

182 Posts

Posted - 10/26/2012 :  04:12:22  Show Profile  Reply with Quote
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
3371 Posts

Posted - 10/26/2012 :  04:24:08  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

hspatil31
Posting Yak Master

182 Posts

Posted - 10/26/2012 :  04:45:45  Show Profile  Reply with Quote
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
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3371 Posts

Posted - 10/26/2012 :  05:16:36  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000