Looking for some help with a SELECT query..

[A JM]

I have 2 tables that I want to pull some results from. The fields from the 2 tables that I'm mostly concerned with are as follows.

 

UserTable

user_id

home_id

user_name

home_descr

...

 

EntryTable

item_number

rack_position

user_id

home_id

...

 

Essentially I want to pull the user_name and home_descr from the UserTable with a SELECT query that pulls from the EntryTable sort of like below. I'm just terrible at joining tables together and could use some help.

 

SELECT item_number, rack_position, user_name, home_descr FROM EntryTable

[JonnoTheDev]

SELECT u.user_name, e.* FROM UserTable u INNER JOIN EntryTable e ON (u.user_id = e.user_id)

[A JM]

This works perfectly for the user_id but not for the home_id.

 

SELECT u.user_name, e.* FROM UserTable u INNER JOIN EntryTable e ON (u.user_id = e.user_id)

 

I think I also need a query like this incorporated into the first query - is that possible or am I thinking about this incorectly?

 

SELECT u.home_descr, e.* FROM UserTable u INNER JOIN EntryTable e ON (u.home_id = e.home_id)

 

A JM,

[JonnoTheDev]

SELECT u.user_name, e.* FROM UserTable u INNER JOIN EntryTable e ON (u.user_id = e.user_id AND u.home_id = e.home_id)

[A JM]

I still think this has a problem in that as long as the user_id and home_id are the same user it will work but if the user_id and the home_id are different users it wouldn't, does that make sense?

 

With a slight modification to the UserTable - that's my fault I misled you.

 

UserTable

user_id

user_name

home_descr

...

 

EntryTable

item_number

rack_position

user_id 'from UserTable user_id

home_id 'from UserTable user_id

...

 

For instance from the EntryTable the user_id and home_id can be different. Ultimately I should be able to pull any field from the UserTable given either the user_id or the home_id from the EntryTable.

 

user_id=1

home_id=1

 

should equate to:

user_name=fred_flinstone

user_name=fred_flinstone

 

user_id=1

home_id=2

 

should equate to:

user_name=fred_flinstone

user_name=barney_rubble

 

user_id=2

home_id=2

 

should equate to:

user_name=wilma_flinstone

user_name=barney_rubble

 

user_id=3

home_id=5

 

should equate to:

user_name=bam_bam

user_name=pebbles

[JonnoTheDev]

Try

 

SELECT e.user_id, e.home_id, u.user_name AS user1, h.user_name AS user2

FROM EntryTable e INNER JOIN UserTable u ON(e.user_id = u.user_id) INNER JOIN UserTable h ON(e.home_id = u.user_id)

[A JM]

On the second join did you mean to join the h table instead of the u table or does it matter what table is referenced?

 

...INNER JOIN UserTable u ON(e.user_id = u.user_id) INNER JOIN UserTable h ON(e.home_id = h.user_id)

 

Thanks.

 

A JM,

[A JM]

This works perfectly with one exception - you knew it was coming... sorry 

 

SELECT e.user_id, e.home_id, u.user_name AS user1, h.user_name AS user2
FROM EntryTable e INNER JOIN UserTable u ON(e.user_id = u.user_id) INNER JOIN UserTable h ON(e.home_id = h.user_id)

 

By default the the EntryTable has both fields user_id and home_id set to zero(0) and there is no zero(0) in the UserTable so unless both fields are populated I get nothing, anyway you can think of around this?

 

A JM,

[JonnoTheDev]

Use LEFT JOINS

[A JM]

Neil,

 

Since I suck at joining tables and the like can you explain a little why a left join seems to have worked?

 

Thanks for all your help.

 

A JM,

[JonnoTheDev]

http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

[fenway]

INNER JOIN -- both tables have to have matching rows

LEFT JOIN -- non-matching rows from the table on the "right" will be NULLed out, but still "found".