Join Results of Two Queries?

[VikR0003]

I have two queries. Each of them works correctly by itself. I need to join the results.

I thought I might use SELECT AS to join the results. Here is the current mySQL syntax:

-----
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
-----

As I noted, the queries are working fine. I just need to find the correct way to join them. When I run this code, I get this error message:

> 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

How can I correct this? Thanks very much in advance to all for any info.

 

[d0nny]

I am certainly no expert in this area, but I would initially think a VIEW would solve this.

 

[johnwm]

You are trying to use a column alias on a multicolumn result. See

http://dev.mysql.com/doc/refman/5.1/en/select.html

for details of Select syntax

If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[VikR0003]

johnwm, since select as is not the correct approach, what would be the correct approach to joining these two queries?

d0nny, I will research VIEW now.

Thanks very much in advance to all for any info.

 

[VikR0003]

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;

d0nny, thanks very much for this suggestion!

 

[d0nny]

Only too glad to help.
As I say, I am not an expert in this area and I'm sure there are guys on this board who have forgotten more about mySQL than I know, but I've used Views in the past to create data that 'feels' dynamic, with very good results.

 

[msndba]

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

First off, i've been taught that it's very bad SQL to use "select *" for a repeated query. Reason being, table structures change and you can throw things off pretty badly when that happens and you've chosen that route.

I am no expert on MySQL at all, but for TSQL you would use

select new_alloys_added_by_users.*, user_prefs_for_alloys.*
from new_alloys_added_by_users join ratings_for_fz_scores on
on new_alloys_added_by_users.Alloy_ID_No = ratings_for_fz_scores.Alloy_ID_No
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)

Not knowing what columns there are in each table, you could pare this down by choosing only the relevant columns from each table. More typing, but less work by the database.