0

I am an absolute newbie with MySQL and other than basic select functions I don't know much more! I have been reading various posts about joining Queries etc But it's currently going over my head far too much to be able to build what I need and I'm short of time to learn!

So would anyone be able to pull me together a query to work out the below, please?

I have 3 tables which contain fields below that I need to use.

Accounts

i_account,
id,
i_env (this value would be =2),
first_usage_time

UA

i_ua,
mac,
inventory_id,
description

UA_Links

i_ua,
i_account

So as you can see the table UA_Links contains the two fields that tie the UA and Accounts tables together.

What I need to do is, output from the Accounts table, id and first_usage along with the mac, inventory_id and description from the UA table.

Hopefully that makes sense?

Many thanks in advance Mike

Improve this question
0

4 Answers 4

Reset to default
1

The most simple way of doing this is to create a simple join.

SELECT Account.id, Accounts.first_usage_time, UA.mac, UA.inventory_id, UA.description 
FROM Accounts, UA, UA_Links
WHERE Accounts.id = UA_Links.i_account 
AND UA.i_ua = UA_Links.i_ua

A little explanation: you can use a comma to select multiple tables. If you want to select a column you need to use the syntax {tablename}.{column_name}. To connect the 3 tables together you need to link the ID's in the WHERE statement.

I'm not totally sure if I got the correct columns, you might want to check that (are UA.i_ua and UA_Links.i_ua the same?).

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

OK this sort of makes sense to me now, thank you. But the Select * is going to pull everything out of Accounts, UA and UA_Links, if I'm understanding correctly? I only need id and first_usage from Accounts table and mac, inventory_id and description from the UA table.
0

May be this is can help..:)

select a.id,a.first_usage_time
from Accounts a
left join UA_Links b
on b.i_account=a.i_account
left join UA c
on c.i_ua=b.i_ua
Improve this answer

Comments

0

This is a very simple join, all you need is to tell the query which field matches which of the other table: 

select * from accounts as a inner join ua_links as l 
on a.i_account = l.i_account 
Inner join ua on l.i_ua = ua.i_ua
Improve this answer

Comments

0

Hope this will work for your needs,

select acc.id as YOURALIAS, acc.first_usage_time as YOURALIAS, 
ua.mac as YOURALIAS, ua.inventory_id as YOURALIAS, 
ua.description as YOURALIAS 
from ua INNER JOIN ua_links ON ua.i_ua = ua_links.i_ua 
inner join accounts acc on ua_links.i_account = acc.i_account

It is a best practice to use aliases, you can find more on http://www.w3schools.com/sql/sql_alias.asp .

Improve this answer

2 Comments

Thanks Alin, that seems straightforward. I'll have a play around with that now.
OK got it working, thank you! I had to edit it slightly for capitalisation of the table names but the followoing gave the required output ....... select acc.id as accountid, acc.first_usage_time as firstuse, UA.mac as devicemac, UA.inventory_id as inventoryid, UA.description as inventorydesc from UA INNER JOIN UA_Links ON UA.i_ua = UA_Links.i_ua inner join Accounts acc on UA_Links.i_account = acc.i_account where acc.i_env = 2 limit 1000;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.