Select data from three tables

Question

question

coolcurrent asked Jun 14, '10

Select data from three tables

Hello guys,

I have been trying to select data from three tables, but am not good at joins The initial parameter is token. I have this tables

devices

user_id

token

users

first_name << this will be selected where from_id =user_id

last_name << this will be selected where from_id =user_id

user_id

messages

from_id

to_id << this is user_id from in devices & users

subject

read_dt

The final table will look like this

Result

first_name

last_name subject

select joins

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-06-14T03:25:03Z

David Wimbush answered Jun 14, '10

This should put you on the right path: http://msdn.microsoft.com/en-us/library/ms191517.aspx

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-06-14T04:18:35Z

ThomasRushton answered Jun 14, '10

This should do the job:

select u.first_name, u.last_name, m.subject
from 
messages m LEFT JOIN users u ON m.to_id = u.user_id
LEFT JOIN devices on d.user_id = u.user_id
WHERE
d.token = <initial token here>

(I've used table aliases to save a bit of typing.)

However, M'Learned Colleague Mr Wimbush has linked to the MS documentation on how to do joins... http://msdn.microsoft.com/en-us/library/ms191517.aspx

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

coolcurrent commented · Jun 14, 2010 at 08:12 AM

Thanks for replying. Am polling data from three tables!. First i have to get user_id form table devices using token as parameter, then i need to get from_id,subject,message from message table with to_id = user_id lastly, i need to get first_name,last_name from users table with user_id = from_id i hope its clearer now

0 ·

Grant Fritchey ♦♦ commented · Jun 14, 2010 at 08:48 AM

The answer above uses three tables. If that's confusing to you, might I recommend you pick a good book on T-SQL basics. I'd recommend Itzik Ben-Gan's T-SQL Fundamentals. Excellent book.

0 ·

ThomasRushton ♦♦ commented · Jun 14, 2010 at 09:25 AM

Perhaps this would be more your thing? SELECT u.first_name, u.last_name, m.subject FROM messages m, users u, devices d WHERE u.user_id = m.to_id and d.user_id = u.user_id and d.token =

0 ·

Answer 3 · 2010-06-14T04:19:16Z

Magnus Ahlkvist answered Jun 14, '10

Something like this perhaps:

select first_name, last_name, subject
from users u inner join messages m on u.user_id = m.to_id
inner join token t on t.user_id = u.user_id
where t.token = <some_token>

I think that's what you asked for, if it's the other way around with to_id and from_id, you'll probably be able to figure it out :)

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

coolcurrent commented · Jun 14, 2010 at 08:11 AM

Thanks for replying. Am polling data from three tables!. First i have to get user_id form table devices using token as parameter, then i need to get from_id,subject,message from message table with to_id = user_id lastly, i need to first_name,last_name from users table with user_id = from_id i hope its clearer now

0 ·

Grant Fritchey ♦♦ commented · Jun 14, 2010 at 08:49 AM

This answer, like the other one, does what you're asking for. It's just using an INNER JOIN instead of an OUTER JOIN.

0 ·

Magnus Ahlkvist commented · Jun 14, 2010 at 05:51 PM

Ok, Then I think I get it. Try this: SELECT first_name, last_name, subject FROM devices d INNER JOIN messages m ON d.user_id = m.to_id INNER JOIN users u ON u.user_id = m.from_id WHERE d.token =

0 ·

question