need help with SQL query - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

need help with SQL query

Author

Topic

Glyc07
Starting Member

4 Posts

Posted - 2009-06-02 : 05:03:26

Hello
I've got 2 tables:
Entries
Entry ID | Body
1 | BodyOfEntry1
2 | BodyOfEntry2
3 | BOdyOfEntry3
....

Pictures
PictureID | PictureURL
1 | http://...
2 | http://...
...

both are connected many to many, so I'm using third table:
Entries_Pictures
EntryID | PictureID
1 | 2
1 | 1
2 | 1
3 | 2
My job is to get all Pictures related with EntryID
what I'm doing is getting all PicturesID from Entries_Pictures table
[ WHERE (EntryID = @EntryID) ] and than for each PictureID I select PictureURL from Pictures table [ WHERE (PictureID = @PictureID) ]
it's not the fastest way, thats why I'm asking how can I upgrade it ?
Is there any way to send table of PictureIDs and with 1 round to database select all interested PictureURLs ?
I'm using DataSet in ASP.NET

thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-02 : 05:07:10

[code]
SELECT e.EntryID,
e.Body,
p.PictureId,
p.PictureURL
FROM Entries e
INNER JOIN Entries_Pictures ep ON ep.EntryID = e.EntryID
INNER JOIN Pictures p ON ep.PictureID = p.PictureID
WHERE e.EntryID = @EntryID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Glyc07
Starting Member

4 Posts

Posted - 2009-06-02 : 05:32:17

many thanks!, exactly what I was looking for, but I couldn't create it on my own ;)

Glyc07
Starting Member

4 Posts

Posted - 2009-06-02 : 10:28:19

I've got another question.
with this query:


SELECT		e.EntryID, 
		e.Body, 
		p.PictureId, 
		p.PictureURL,
                s.Body
FROM    	Entries e
INNER JOIN	Entries_Pictures ep	ON	ep.EntryID	= e.EntryID
INNER JOIN 	Pictures p		ON	ep.PictureID	= p.PictureID
INNER JOIN 	Samples s		ON	s.EntryID	= e.EntryID
WHERE		e.EntryID	= @EntryID

what happens here is when there are 0 samples for EntryID query doesn't return anything, how can I get PictureURL and sample's Body if both exist and only PictureURL when sample doesn't exist or only sample's Body if PictureURL doesn't exist?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 12:26:58

change inner to left join


SELECT		e.EntryID, 
		e.Body, 
		p.PictureId, 
		p.PictureURL,
                s.Body
FROM    	Entries e
INNER JOIN	Entries_Pictures ep	ON	ep.EntryID	= e.EntryID
LEFT OUTER JOIN 	Pictures p		ON	ep.PictureID	= p.PictureID
LEFT OUTER JOIN 	Samples s		ON	s.EntryID	= e.EntryID
WHERE		e.EntryID	= @EntryID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources