[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 578
  • Last Modified:

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
0
Fernanditos
Asked:
Fernanditos
  • 7
  • 7
1 Solution
 
lwadwellCommented:
Sure.  A very basic starting query would be:

SELECT *
FROM user_user uu
JOIN history_usr hu ON uu.id_usr = hs.userid_his
0
 
lwadwellCommented:
What do you want the query to do:
- select only certain columns?
- filter out certain rows from either table?
- something else?
0
 
FernanditosAuthor Commented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
lwadwellCommented:
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
0
 
jtdebeerCommented:
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
0
 
FernanditosAuthor Commented:
I get similar error now:

#1054 - Unknown column 'hu.userid_his' in 'on clause'

Any idea?

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

#1054 - Unknown column 'userid_his' in 'on clause'
0
 
lwadwellCommented:
OK ... but the syntax error needs to be fixed first.
SELECT *
FROM user_usr
LEFT JOIN history_usr ON id_usr = userid_his
0
 
FernanditosAuthor Commented:
I still get:
#1054 - Unknown column 'userid_his' in 'on clause'

The colum userid_his exist history_usr table, there is no misspelling.
0
 
lwadwellCommented:
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
0
 
FernanditosAuthor Commented:
I attach the sample database dump ( both tables and 1 record enough to test )


Thank you
2tables.sql
0
 
FernanditosAuthor Commented:
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 ?
0
 
lwadwellCommented:
Ahhhh ... if you look at the table definition, there is a space in-front of the column name
CREATE TABLE IF NOT EXISTS `history_usr` (
  `history_usr` int(15) NOT NULL AUTO_INCREMENT,
  ` userid_his` int(11) NOT NULL,
  `userlevel_his` int(3) NOT NULL,
  `upgradedate_his` date NOT NULL,
  `expiredate_his` date NOT NULL

Query is:
SELECT *
FROM user_usr
LEFT JOIN history_usr ON id_usr = ` userid_his`
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now