question

coolcurrent avatar image
coolcurrent asked

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

selectjoins
10 |1200

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

David Wimbush avatar image
David Wimbush answered

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

10 |1200

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

ThomasRushton avatar image
ThomasRushton answered

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 comments
10 |1200

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

coolcurrent avatar image coolcurrent commented ·
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 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
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 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

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 comments
10 |1200

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

coolcurrent avatar image coolcurrent commented ·
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 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
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 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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