Link to home
Start Free TrialLog in
Avatar of Fernanditos
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
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

Sure.  A very basic starting query would be:

SELECT *
FROM user_user uu
JOIN history_usr hu ON uu.id_usr = hs.userid_his
What do you want the query to do:
- select only certain columns?
- filter out certain rows from either table?
- something else?
Avatar of Fernanditos
Fernanditos

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.
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
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
I get similar error now:

#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".
Please let's use LEFT JOIN which is what I need.
With you previous query without any alias I got:

#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
I still get:
#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
I attach the sample database dump ( both tables and 1 record enough to test )


Thank you
2tables.sql
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 ?
ASKER CERTIFIED SOLUTION
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial