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

[VikR]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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!