CODEWITHSUNDEEP
mysqldatabasesubqueryunion
stackoverflow
stackoverflow

Reputation: 19434

In MySQL how do I create JOIN within a subquery?

Working sample using one Table

SELECT t.* FROM (
  SELECT
    TITLE.name, 
    (TITLE.value-TITLE.msp) AS Lower, 
    (TITLE.value+TITLE.msp) AS Upper,
    (TITLE.value) AS Value
  FROM TITLE 
) t
WHERE 98 BETWEEN t.Lower AND t.Upper
ORDER BY ABS(98 - t.Value) ASC
LIMIT 5

Desired example working with 3 tables (Needs fixed/help)

SELECT t.* FROM (
  SELECT
    TITLE.name, ALBUM.year, GENRE.Type
    (TITLE.value-TITLE.msp) AS Lower, 
    (TITLE.value+TITLE.msp) AS Upper,
    (TITLE.value) AS Value
  FROM TITLE, ALBUM, GENRE 
) t
WHERE ALBUM.ID=GENRE.ID AND TITLE.ID=ALBUM.ID  
AND 98 BETWEEN t.Lower AND t.Upper
ORDER BY ABS(98 - t.Value) ASC;

I get the following error:

ERROR 1054 (42S22): Unknown column 'ALBUM.ID' in 'where clause'

Upvotes: 0

Views: 134

Answers (3)

Konerak
Konerak

Reputation: 39763

This is logical: your subquery creates one table t, and afterwords you try to refer to a table ALBUM. There is no ALBUM, there is only t

The quickest fix it to move the WHERE clause for the JOIN where it belongs: in the subquery.

SELECT t.* FROM (
  SELECT
    TITLE.name, ALBUM.year, GENRE.Type
    (TITLE.value-TITLE.msp) AS Lower, 
    (TITLE.value+TITLE.msp) AS Upper,
    (TITLE.value) AS Value
  FROM TITLE, ALBUM, GENRE 
  WHERE ALBUM.ID=GENRE.ID AND TITLE.ID=ALBUM.ID  
) t
WHERE 98 BETWEEN t.Lower AND t.Upper
ORDER BY ABS(98 - t.Value) ASC;

The query itself ain't that beautiful though... why not try this instead:

  SELECT
    TITLE.name, ALBUM.year, GENRE.Type
    (TITLE.value-TITLE.msp) AS Lower, 
    (TITLE.value+TITLE.msp) AS Upper,
    (TITLE.value) AS Value
  FROM TITLE
   JOIN ALBUM
    ON TITLE.ID=ALBUM.ID  
   JOIN GENRE 
    ON ALBUM.ID=GENRE.ID 
  WHERE 98 BETWEEN Lower AND Upper
  ORDER BY ABS(98 - Value) ASC;
  • Removed the unnecessary subquery
  • Used SQL-92 style JOIN instead of SQL-89 style JOIN (cartesian product with a where clause)

Upvotes: 3

Joe Stefanelli
Joe Stefanelli

Reputation: 135789

Move the WHERE clause to the inner query.

SELECT t.* FROM (
  SELECT
    TITLE.name, ALBUM.year, GENRE.Type
    (TITLE.value-TITLE.msp) AS Lower, 
    (TITLE.value+TITLE.msp) AS Upper,
    (TITLE.value) AS Value
  FROM TITLE, ALBUM, GENRE 
  WHERE ALBUM.ID=GENRE.ID AND TITLE.ID=ALBUM.ID  
) t
AND 98 BETWEEN t.Lower AND t.Upper
ORDER BY ABS(98 - t.Value) ASC;

Upvotes: 1

rsenna
rsenna

Reputation: 11964

Look at your query: you are defining a sub-query, called T, that does a cartesian product between tables TITLE, ALBUM and GENRE. Then you select from that sub-query, and try to apply a WHERE clause over TITLE, ALBUM and GENRE... But those tables are not in scope anymore!

Try this instead:

SELECT t.* FROM (
  SELECT
    TITLE.name, ALBUM.year, GENRE.Type
    (TITLE.value-TITLE.msp) AS Lower, 
    (TITLE.value+TITLE.msp) AS Upper,
    (TITLE.value) AS Value
  FROM TITLE, ALBUM, GENRE 
  WHERE ALBUM.ID=GENRE.ID AND TITLE.ID=ALBUM.ID  
) t
WHERE 98 BETWEEN t.Lower AND t.Upper
ORDER BY ABS(98 - t.Value) ASC;

Upvotes: 2

Related Questions