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

General SQL Server Forums

New to SQL Server Programming

Inner join help

Author

Topic

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2009-04-01 : 05:45:04

Can I limit the inner join with the top 1 from tb_booking_email?
tb_booking_email has multiple rows I want to get the last entry to inner join but don't know how to code this.

The tb_booking_email has an ID field as well. So basically I want this to inner join on the booking_emails_booking_id but only select the one with the highest ID field.

SELECT tb_booking_check.*, tb_booking_email.*, tb_owner_email.* from tb_booking_check
INNER JOIN tb_booking_email ON tb_booking_check.bc_booking_id = tb_booking_email.booking_emails_booking_id

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2009-04-01 : 06:10:34

Select tbc.*, tbe.*
from tb_booking_check tbc,
tb_booking_email tbe
where tbc.bc_booking_id =
(Select Max(1) booking_emails_BookingId from tb_booking_email)

Assuming that your bookingId is int right?

Eugene

check out my blog at http://www.aquariumlore.blogspot.com

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2009-04-01 : 06:27:37

Sorry Eugene I tried that method but it doesn't return anything

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-01 : 06:55:49

You'll have to change your tb_booking_email dataset to have only the rows with max id and then join. Something like,

select 
	* 
from 
	tb_booking_check

	join

	(
		select 
			a.col1, a.col2, a.col3..,a.booking_emails_booking_id 
		from 
			tb_booking_email a 
		where 
			booking_emails_booking_id=
				(
				select 
					max(booking_emails_booking_id) 
				from 
					tb_booking_email 
				where 
					col1=a.col1 and col2=a.col2 and col3=a.col3 ....
				)
	)tb_booking_email
	 on  tb_booking_check.bc_booking_id=tb_booking_email.booking_emails_booking_id ...

Makes sense ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-01 : 12:57:03

if sql 2005 you can use this


SELECT tb_booking_check.*, b.*, tb_owner_email.* 
from tb_booking_check
CROSS APPLY (SELECT TOP 1 * FROM tb_booking_email WHERE booking_emails_booking_id=tb_booking_check.bc_booking_id ORDER BY booking_emails_booking_id DESC)b
....

godspeedba
Yak Posting Veteran

90 Posts

Posted - 2009-04-02 : 04:08:51

Yay, got it working, thank you so much guys :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-03 : 12:38:20

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources