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
 join query help

Author  Topic 

diyeys
Starting Member

11 Posts
Posted - 2007-09-20 : 04:12:07
Hello everyone,

I am stuck with a query, i cannot make it work.

I have 2 tables:

NEWS
ID | TITLE | TEXT
-------------------
1 | title1 | text1
2 | title2 | text2
3 | title3 | text3

IMAGES
ID | IDNEW | PATH
------------------
1 | 1 | path1
2 | 1 | path2
3 | 1 | path3
4 | 2 | path4
5 | 2 | path5
6 | 3 | path6


I need to get all the news, and for each new i need the id of only 1 of the possible images.

I thought it could be an inner join but it does not work:

SELECT NEWS.*, IMAGES.ID AS IDIMAGE
FROM NEWS
INNER JOIN IMAGES
ON NEWS.ID = IMAGES.IDNEW

Can someone please help me with this query?

Thanks in advance.

mwjdavidson
Aged Yak Warrior

735 Posts
Posted - 2007-09-20 : 04:20:53
If you don't care which Image ID you return:

SELECT     NEWS.ID, 
           NEWS.TITLE,          
           NEWS.TEXT, 
           MIN(IMAGES.ID) AS IDIMAGE 
FROM       NEWS
INNER JOIN IMAGES
ON         NEWS.ID = IMAGES.IDNEW
GROUP BY   NEWS.ID, 
           NEWS.TITLE, 
           NEWS.TEXT


Mark
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts
Posted - 2007-09-20 : 04:20:55
[code]SELECT NEWS.*, IMAGES.ID AS IDIMAGE
FROM NEWS
INNER JOIN
(select * from Images t2
where t2.Path = (select max(Path) from Images t3 where t3.IDNEW = t2.IDNEW)) as IMAGES
ON NEWS.ID = IMAGES.IDNEW[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

diyeys
Starting Member

11 Posts
Posted - 2007-09-20 : 04:25:11
Thanks a lot for the help.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site