CODEWITHSUNDEEP
mysqlsql
joseagaleanoc
joseagaleanoc

Reputation: 615

How can I select from two tables?

If I have these two tables:

table_user
Id   name   lname
1    Mark   Brown
2    Martha Fox

table_score_start:
user_Id  score
2        5

table_score_last:
user_Id  score
1        3
2        4

How can I show the following query result?

Id name lname score score 1 Mark Brown 3 2 Martha Fox 5 4

I tried 

    SELECT table_user.Id, table_user.name, table_user.lname, table_score_start.score,
 table_score_last.score FROM table_user, table_score_start, table_score_last

but it doesnt work

I also tried 

    SELECT table_user.Id, table_user.name, table_user.lname, table_score_start.score,
 table_score_last.score FROM table_user, table_score_start, table_score_last WHERE table_user.Id = table_score_start.user_Id

I want to show all the records even thought including those that are not in one or two of the tables tables table_score_start and table_score_last

Upvotes: 0

Views: 89

Answers (7)

Mehdi Karamosly
Mehdi Karamosly

Reputation: 5428

try the below query:

SELECT u.Id, u.name, u.lname, s.score, l.score FROM table_user u, 
table_score_start s, table_score_last l WHERE u.id = s.user_id
AND u.id = l.user_id

Or using joins :

SELECT u.Id, u.name, u.lname, s.score, l.score FROM table_user u
INNER JOIN table_score_start s ON (u.id = s.user_id)
INNER JOIN table_score_last l ON ( u.id = l.user_id)

You can read more about MySql JOIN in this article: http://dev.mysql.com/doc/refman/5.0/en/join.html

Upvotes: 2

Andrey Volk
Andrey Volk

Reputation: 3549

select a.Id, a.name, a.lname, b.score as start_score, c.score as last_score from table_user a 
inner join table_score_start b on (a.Id = b.user_Id) 
inner join table_score_last c on (a.Id = c.userId)

inner join or left join depends on your needs.

Upvotes: 2

Patrick Kostjens
Patrick Kostjens

Reputation: 5105

In other answers I see INNER JOIN, but since you also want to see the records that don't have a start or end score (or both), you should be using a LEFT JOIN like this:

SELECT a.Id, a.name, a.lname, b.score as start_score, c.score as last_score 
FROM table_user a 
LEFT join table_score_start b on (a.Id = b.user_Id) 
LEFT join table_score_last c on (a.Id = c.user_Id)

Upvotes: 0

Declan_K
Declan_K

Reputation: 6826

When you are running a SELECT against multiple tables, you should also include the JOIN condition between those tables. Here's somewhere to start reading up on JOINS

Try the following. Please also not the use of aliases for the tables, which just makes the code easier to read, but has no impact on the execution.

SELECT  u.Id
        ,u.name
        ,u.lname
        ,ss.score
        ,sl.score
FROM    table_user u
INNER JOIN
        table_score_start ss
ON      ss.user_ID = u.Id
INNER JOIN
        table_score_last sl
ON      sl.user_ID = u.Id

Upvotes: 0

FreeMan
FreeMan

Reputation: 5687

Something like this ought to do the trick:

SELECT u.ID, u.name, u.lname, start.score, last.score
FROM table_user AS u LEFT JOIN table_Score_Start AS Start on u.ID = Start.ID
LEFT JOIN table_Score_last AS Last on u.id = Last.ID

It's off the top of my head, but that should get you in the ballpark. You may have to make some MySQL syntax tweaks, I've been working in SQL Server.

Upvotes: 0

phpisuber01
phpisuber01

Reputation: 7715

SELECT `user`.*, `start`.score, `last`.score 
FROM table_user `user`, table_score_start `start`, table_score_last `last` 
WHERE `start`.user_Id = `user`.Id 
AND `last`.user_Id = `user`.Id;

Upvotes: 0

fahim74
fahim74

Reputation: 55

Use mysql JOIN ....

here is a simple guide for this ...

http://www.copterlabs.com/blog/optimizing-sql-queries-with-joins/

Upvotes: 0

Related Questions