[SOLVED] Left Join Not Acting Like I Expected it To? (updated with table structure info)

VikR · December 31, 2008

I have 3 tables:

"new_alloys_added_by_users" includes one record per each alloy added by each user.
"user_prefs_for_alloys" has multiple records for each user and for each alloy.
"ratings_for_alloy_scores" has at most one record for each user/alloy combination, but not all alloys are rated by all users.

I want to do a query that will:

Select all the records from "new_alloys_added_by_users" that are marked as being used by the id of the current user (new_alloys_added_by_users.id).
Join to each of those records, the record from "user_prefs_for_alloys" for that alloy (user_prefs_for_alloys.alloy_ID_No) and for that user (user_prefs_for_alloys.id = 1 in this example, since 1 is the id of the current user in this example).
Join to each of these records, the record from "ratings_for_alloy_scores" for that alloy (ratings_for_alloy_scores.alloy_ID_No) as rated by that user (ratings_for_alloy_scores.id_of_user_who_provided_this_rating = 1)... if such a rating is available.

Here's my mySQL query:

SELECT * 
FROM (new_alloys_added_by_users
JOIN user_prefs_for_alloys ON user_prefs_for_alloys.alloy_ID_No = new_alloys_added_by_users.alloy_ID_No)
LEFT JOIN ratings_for_alloy_scores ON ratings_for_alloy_scores.alloy_ID_No = new_alloys_added_by_users.alloy_ID_No
WHERE (user_prefs_for_alloys.id = 1)
AND (ratings_for_alloy_scores.id_of_user_who_provided_this_rating = 1)

This is working, except that it's only giving me records where there is a rating for an alloy in the "ratings_for_alloy_scores" table. I thought a left join would also return records where there was no record in the table being left joined, in this case the "ratings_for_alloy_scores" table, and just mark the fields from the "ratings_for_alloy_scores" table as NULL.

If I remove the line:

AND (ratings_for_alloy_scores.id_of_user_who_provided_this_rating = 1)

...then I get records returned for all ratings by all users, instead of just ratings where ratings_for_alloy_scores.id_of_user_who_provided_this_rating = 1.

What am I missing?

Thanks very much in advance to all for any info!

-Vik

Relevant info:

MySQL: Client API version 5.0.41

TABLE "new_alloys_added_by_users"

alloy_ID_No int 11

Long_Desc varchar 200

brand_name varchar 75

product_name varchar 75

description varchar 35

id_of_user_who_added_this int 11

score int 11

date_added varchar 12

let_other_people_use_this_info tinyint 1

TABLE "user_prefs_for_alloys"

id int 11

NDB_No int 11

alloy_ID_No int 11

min float 0

max float 0

final_amount float 0

units_id varchar 15

used_in_last_alloy_plan tinyint 1

TABLE "ratings_for_scores"

alloy_ID_No int 11

id_of_user_who_provided_this_rating int 11

rating_was_a_plus tinyint 1

gwydionwaters · December 31, 2008

what happens if you try this

SELECT * 

FROM new_alloys_added_by_users

JOIN user_prefs_for_alloys ON user_prefs_for_alloys.alloy_ID_No = new_alloys_added_by_users.alloy_ID_No

INNER JOIN ratings_for_alloy_scores ON ratings_for_alloy_scores.alloy_ID_No = new_alloys_added_by_users.alloy_ID_No

WHERE user_prefs_for_alloys.id = 1

AND ratings_for_alloy_scores.id_of_user_who_provided_this_rating = 1

it is what i would use

VikR · December 31, 2008

what happens if you try this

Thanks very much for the input. If I try the code you suggested, I only get records where id_of_user_who_provided_this_rating = 1. I do not yet get records for other alloys, which have not yet been rated, with id_of_user_who_provided_this_rating = NULL. How can I correct this?

gwydionwaters · December 31, 2008

hmmm, i was re-reading your original post and i was becoming confused. i see in the table user_prefs_for_alloys(which i assume is a table containing information for each user?) there is the same alloy_id_no as in the new_alloys_added_by_users. if my assumption is correct i was wondering why a user is being associated with one alloy instead of just associating a user with every alloy of theirs in the new_alloys_added_by_users table, which may be creating a problem when trying to associate them

VikR · December 31, 2008

You may be right. Let me see if I follow. Here's what's going on with the table structure.

"user_prefs_for_alloys" includes one row for each alloy, for each user. I.e. a user can have preferences for the use of any number of alloys. The alloy_id_no indicates which alloy the row contains preferences for.

"new_alloys_added_by_users" contains new alloys entered by users. Each user can enter any number of alloys.

"ratings_for_scores" is where each user can rate any of the alloys by giving it a thumbs up or a thumbs down.

The reason there is a separate table for "user_prefs_for_alloys" is that all users can have preferences for the use of all alloys, whether they are the user who added that particular alloy or not.

Does this make sense? Am I missing something in the table structure that is keeping the mySQL syntax from working as expected?

Thanks very much for your thoughts.

gwydionwaters · January 1, 2009

ok, i'm with you that far now. i'm trying to work it out but i get confused without the actual database to try. i'll let you know if i figure it out, i might just recreate your tables on my server in the end

VikR · January 1, 2009

I'm working on accomplishing this by doing two separate queries, and then joining the results. Something like this...

select  *  from new_alloys_added_by_users  left join ratings_for_fz_scores on new_alloys_added_by_users.Alloy_ID_No = ratings_for_fz_scores.Alloy_ID_No AS NewAlloysAndRatings

SELECT * 
FROM user_prefs_for_alloys
JOIN new_alloys_added_by_users ON user_prefs_for_alloys.Alloy_ID_No = new_alloys_added_by_users.Alloy_ID_No
WHERE (user_prefs_for_alloys.id = 1)
AND (user_prefs_for_alloys.Alloy_ID_No >0)
AS UserPrefsAndNewAlloys

SELECT *
from NewAlloysAndRatings JOIN UserPrefsAndNewAlloys on NewAlloysAndRatings.Alloy_ID_No = UserPrefsAndNewAlloys.Alloy_ID_No

I don't have the syntax for the AS keyword right yet. When I enter:

select  *  from new_alloys_added_by_users  left join ratings_for_fz_scores on new_alloys_added_by_users.Alloy_ID_No = ratings_for_fz_scores.Alloy_ID_No AS NewAlloysAndRatings

...I get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS NewAlloysAndRatings' at line 1

Do you know how I can correct this syntax error by any chance?

xtopolis · January 1, 2009

You can't assign an alias using AS at the area of the query. Remove it. If you wanted to alias something, do it in the "SELECT * .." area, in this situation.

Also, if you want more help, read this stickies on how to ask a MYSQL question and post your structures correctly.

VikR · January 1, 2009

From the stickies:

the table structure & column indexes of the relevant tables [via SHOW CREATE TABLE is preferred]

It says that posting the structures via SHOW CREATE TABLE is preferred, but not required, so I believe I am in compliance.

Can you be more specific about how to correct the syntax I posted? I have tried moving the AS section into the "SELECT * " area, but I'm still getting the same error. I would really appreciate advice on this, as well as on improved compliance with the stickies.

xtopolis · January 1, 2009

You posted your structure, but most people here aren't going to take the time to try to recreate those tables by reading that. A mysql dump / show create table output that we can simply COPY/PASTE to recreate and test your tables makes it soooo much easier. While other sections of this forum advise against posting a lot of code, in this section it is a lot easier if you give us the structure and SOME[not all] relevant data in dump form.

As for your syntax, you can't SELECT * and try to alias it. You need to select the column name specifically and then alias it.

SELECT *,columnName as Name FROM yourTable ..

However that would potentially select a duplicate column. It is bad practice to SELECT * unless you actually need all columns in the table.

VikR · January 1, 2009

Thanks for the great info, xtopolis. I am attaching the structures via SHOW CREATE TABLE.

I have two queries that are working correctly. I need to join the results. Since SELECT AS is not the correct approach, what approach may I use for this purpose? Here are the two queries.

SELECT  *  from new_alloys_added_by_users  left join ratings_for_fz_scores on new_alloys_added_by_users.Alloy_ID_No = ratings_for_fz_scores.Alloy_ID_No

SELECT * 
FROM user_prefs_for_alloys
JOIN new_alloys_added_by_users ON user_prefs_for_alloys.Alloy_ID_No = new_alloys_added_by_users.Alloy_ID_No
WHERE (user_prefs_for_alloys.id = 1)
AND (user_prefs_for_alloys.Alloy_ID_No >0)

Both queries are working correctly. What is the correct way to join the results? Thanks very much in advance for any info.

+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| user_prefs_for_alloys | CREATE TABLE `user_prefs_for_alloys` (

`id` int(11) NOT NULL,

`NDB_No` int(11) NOT NULL,

`Alloy_ID_No` int(11) NOT NULL,

`min` float NOT NULL,

`max` float NOT NULL,

`final_amount` float NOT NULL default '0',

`fz_units_id` varchar(15) NOT NULL,

`used_in_last_alloy_plan` tinyint(1) NOT NULL,

KEY `id` (`id`),

KEY `id_2` (`id`,`NDB_No`),

KEY `id_3` (`id`,`Alloy_ID_No`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 rows in set (0.04 sec)

mysql> SHOW CREATE TABLE new_alloys_added_by_users;

+--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| new_alloys_added_by_users | CREATE TABLE `new_alloys_added_by_users` (

`Alloy_ID_No` int(11) NOT NULL auto_increment,

`Long_Desc` varchar(200) NOT NULL,

`brand_name` varchar(75) NOT NULL,

`product_name` varchar(75) NOT NULL,

`description` varchar(35) NOT NULL,

`id_of_user_who_added_this` int(11) NOT NULL COMMENT 'The ID of the User who Added It',

`fz_score` int(11) NOT NULL,

`date_added` varchar(12) NOT NULL default '',

`let_other_people_use_this_info` tinyint(1) NOT NULL,

PRIMARY KEY (`Alloy_ID_No`),

KEY `Alloy_ID_No` (`Alloy_ID_No`),

KEY `Long_Desc` (`Long_Desc`,`id_of_user_who_added_this`),

KEY `Long_Desc_2` (`Long_Desc`,`let_other_people_use_this_info`)

) ENGINE=MyISAM AUTO_INCREMENT=125 DEFAULT CHARSET=latin1 |

+--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 rows in set (0.05 sec)

mysql> SHOW CREATE TABLE ratings_for_fz_scores

-> ;

+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| ratings_for_fz_scores | CREATE TABLE `ratings_for_fz_scores` (

`Alloy_ID_No` int(11) NOT NULL,

`id_of_user_who_provided_this_rating` int(11) NOT NULL,

`rating_was_a_plus` tinyint(1) NOT NULL,

KEY `Alloy_ID_No` (`Alloy_ID_No`,`id_of_user_who_provided_this_rating`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

+-----------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 rows in set (0.04 sec)

mysql>

xtopolis · January 2, 2009

Thank you for posting your structures in a way I could use. (code tags next time! ). Also, for future reference, post some data from each table (using the same mysql dump type format) so that I don't have to add it [so I don't miss dependencies] by hand.. hoping I got it right.

I've gotten kind of lost trying to work out what you wanted, so I tried to go off your top post.

Select all the records from "new_alloys_added_by_users" that are marked as being used by the id of the current user (new_alloys_added_by_users.id)

SELECT * FROM new_alloys_added_by_users naabu
WHERE naabu.id_of_user_who_added_this = 1

Join to each of those records, the record from "user_prefs_for_alloys" for that alloy (user_prefs_for_alloys.alloy_ID_No) and for that user (user_prefs_for_alloys.id = 1 in this example, since 1 is the id of the current user in this example)

SELECT * FROM new_alloys_added_by_users naabu
JOIN user_prefs_for_alloys upfa
USING (alloy_ID_no)
WHERE naabu.id_of_user_who_added_this = 1

Join to each of these records, the record from "ratings_for_alloy_scores" for that alloy (ratings_for_alloy_scores.alloy_ID_No) as rated by that user (ratings_for_alloy_scores.id_of_user_who_provided_ this_rating = 1)... if such a rating is available.

SELECT * FROM new_alloys_added_by_users naabu
JOIN user_prefs_for_alloys upfa
  USING (alloy_ID_no)
JOIN ratings_for_fz_scores rffs
  ON (naabu.id_of_user_who_added_this = rffs.id_of_user_who_provided_this_rating)
WHERE naabu.id_of_user_who_added_this = 1

This being the final querry ^

Test that and tell me if it's anything close to what you wanted. As I stated, I got a bit confused since there are a lot of fields returned. Currently this query should join all those tables based on the given users id.

VikR · January 2, 2009

xtopolis, I am not yet familiar with this syntax. I am running your code and, while it doesn't yet do exactly what I am seeking to do, it seems to be the right approach. What is the name of the syntax used to produce the variables, naabu, upfa, rffs, etc.? I will read up on it.

Thank you very much for this great info!

xtopolis · January 2, 2009

It's just aliasing the table names. Kinda like using the "SELECT column AS columnNewName" but for the table names. I was just lazy and didn't want to write some_long_table_name each time. You can name then almost whatever you want, I just used the first letters of each word.

If you post some proper data and tell me what your expected column results should be, I can take another swing at it.

VikR · January 2, 2009

I think I just got it working using Views. Very cool!

CREATE OR REPLACE VIEW NewAlloysPlusRatings AS 
SELECT  brand_name, date_added, description, new_alloys_added_by_users.Alloy_ID_No, fz_score, id_of_user_who_added_this, id_of_user_who_provided_this_rating, let_other_people_use_this_info, Long_Desc, product_name, rating_was_a_plus
from new_alloys_added_by_users left join ratings_for_fz_scores on new_alloys_added_by_users.Alloy_ID_No = ratings_for_fz_scores.Alloy_ID_No ;

CREATE OR REPLACE VIEW UserPrefsPlusNewAlloys AS 
SELECT id, NDB_No, min, max, final_amount, user_prefs_for_alloys.Alloy_ID_No
FROM user_prefs_for_alloys
JOIN new_alloys_added_by_users ON user_prefs_for_alloys.Alloy_ID_No = new_alloys_added_by_users.Alloy_ID_No
WHERE (user_prefs_for_alloys.id = 1)
AND (user_prefs_for_alloys.Alloy_ID_No >0);

SELECT *
from NewAlloysPlusRatings JOIN UserPrefsPlusNewAlloys on NewAlloysPlusRatings.Alloy_ID_No = UserPrefsPlusNewAlloys.Alloy_ID_No;

Thanks very much to everyone here for your help!

Sign In

[SOLVED] Left Join Not Acting Like I Expected it To? (updated with table structure info)

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information