Fernanditos
asked on
Relational Query - Help
Hi,
I have 2 tables and I want to build a query that returns columns from both tables.
Master table is named: "user_user"
Second table is named: "history_usr"
The relational key field in the second table is named "iduser_his"
I made a dummy mockup to show how the result should look like. Image Attached here.
Could some expert please help me to build this query?
Thank you.
result2.jpg
I have 2 tables and I want to build a query that returns columns from both tables.
Master table is named: "user_user"
Second table is named: "history_usr"
The relational key field in the second table is named "iduser_his"
I made a dummy mockup to show how the result should look like. Image Attached here.
Could some expert please help me to build this query?
Thank you.
result2.jpg
What do you want the query to do:
- select only certain columns?
- filter out certain rows from either table?
- something else?
- select only certain columns?
- filter out certain rows from either table?
- something else?
ASKER
Thank you for your reply. When I run the query:
SELECT *
FROM user_usr uu
JOIN history_usr hu ON uu.id_usr = hs.userid_his
I get this error:
#1054 - Unknown column 'hs.userid_his' in 'on clause'
There is no any misspelling on the column names, what's wrong?
For now I would like to make it work selecting all columns from both tables. After error is fixed I try to select my needed columns only.
Thank you for your great help.
SELECT *
FROM user_usr uu
JOIN history_usr hu ON uu.id_usr = hs.userid_his
I get this error:
#1054 - Unknown column 'hs.userid_his' in 'on clause'
There is no any misspelling on the column names, what's wrong?
For now I would like to make it work selecting all columns from both tables. After error is fixed I try to select my needed columns only.
Thank you for your great help.
There is a misspelling of the alias (my mistake). The corrected SQL would be:
SELECT *
FROM user_usr uu
JOIN history_usr hu ON uu.id_usr = hu.userid_his
SELECT *
FROM user_usr uu
JOIN history_usr hu ON uu.id_usr = hu.userid_his
There are different join options ie left join, right join, outer join.
Please have a look at this post to see exactly which records you need.
http://www.johandebeer.co.za/?p=60
Please have a look at this post to see exactly which records you need.
http://www.johandebeer.co.za/?p=60
ASKER
I get similar error now:
#1054 - Unknown column 'hu.userid_his' in 'on clause'
Any idea?
Thank you
#1054 - Unknown column 'hu.userid_his' in 'on clause'
Any idea?
Thank you
Try without any alias'
SELECT *
FROM user_usr
JOIN history_usr ON id_usr = userid_his
Please confirm the column & table names are spelt correctly again. The screen shot had "userid_his" but your question said "iduser_his".
SELECT *
FROM user_usr
JOIN history_usr ON id_usr = userid_his
Please confirm the column & table names are spelt correctly again. The screen shot had "userid_his" but your question said "iduser_his".
ASKER
Please let's use LEFT JOIN which is what I need.
ASKER
With you previous query without any alias I got:
#1054 - Unknown column 'userid_his' in 'on clause'
#1054 - Unknown column 'userid_his' in 'on clause'
OK ... but the syntax error needs to be fixed first.
SELECT *
FROM user_usr
LEFT JOIN history_usr ON id_usr = userid_his
SELECT *
FROM user_usr
LEFT JOIN history_usr ON id_usr = userid_his
ASKER
I still get:
#1054 - Unknown column 'userid_his' in 'on clause'
The colum userid_his exist history_usr table, there is no misspelling.
#1054 - Unknown column 'userid_his' in 'on clause'
The colum userid_his exist history_usr table, there is no misspelling.
This is odd ... you have selected the right database first? What do you get when you run
SELECT *
FROM history_usr
and
SELECT userid_his
FROM history_usr
SELECT *
FROM history_usr
and
SELECT userid_his
FROM history_usr
ASKER
ASKER
Tes it is odd. When I run this query on my phpmyadmin:
SELECT *
FROM user_usr
LEFT JOIN history_usr ON id_usr = userid_his
I get:
#1054 - Unknown column 'userid_his' in 'on clause'
It is the query who has the problem. Could you please test with my dump ?
SELECT *
FROM user_usr
LEFT JOIN history_usr ON id_usr = userid_his
I get:
#1054 - Unknown column 'userid_his' in 'on clause'
It is the query who has the problem. Could you please test with my dump ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT *
FROM user_user uu
JOIN history_usr hu ON uu.id_usr = hs.userid_his