CODEWITHSUNDEEP
phpmysqlsql
Cheung
Cheung

Reputation: 5

How to write MySQL aliases properly?

For all those that care, I found that I needed to re read the basics on querying. Once sorted, I realised I had a fair few errors throughout my code.

I've got a query I'm trying to build and I'm throwing up error after error regarding to ambiguity. Would anyone kindly show me how to execute MySQL aliases properly?

This is my code so far. It worked fine until I put the join in there. I suspect it's because I'm referencing 'wine' more than once in the same query, but that may be one of many problems. 

$query = 
"SELECT 
    wine_id, 
    wine_name, 
    winery_name, 
    region_name, 
    year, 
    variety
FROM 
    wine AS w, 
    winery, 
    region, 
    grape_variety
JOIN 
    wine 
ON
    grape_variety.variety_id = wine.wine_id
JOIN 
    wine_variety 
ON
    wine.wine_id = wine_variety.variety_id
WHERE 
    winery.region_id = region.region_id
AND 
    wine.winery_id = winery.winery_id"; 

if (!empty($wineName)) {
    $query .= " AND wine_name = '{$wineName}'";
}

if (!empty($wineryName)) {
    $query .= " AND winery_name = '{$wineryName}'";
}

// If the user has specified a region, add the regionName 
// as an AND clause
if (isset($regionName) && $regionName != "All") {
    $query .= " AND region_name = '{$regionName}'"; 
} 

// If the user has specified a variety, add the grapeVariety 
// as an AND clause
if (isset($grapeVariety) && $grapeVariety != "Riesling") {
    $query .= " AND variety = '{$grapeVariety}'"; 
}

Upvotes: 0

Views: 78

Answers (4)

xkeshav
xkeshav

Reputation: 54022

Your query must be like:

SELECT 
    w.wine_id, 
    w.wine_name, 
    wy.winery_name,-- I assume this column from table `wy` 
    r.region_name, -- column from table `region` 
    w.year, 
    w.variety
FROM wine w 
INNER JOIN winery wy ON (wy.winery_id = w.winery_id )
INNER JOIN region r ON ( r.region_id = wy.region_id )
INNER JOIN  grape_variety gv ON (gv.variety_id = w.wine_id)
INNER JOIN  wine_variety wv ON  (wv.variety_id = w.wine_id)
...
append other WHERE conditions here

Note: if you give table structure then it would be more clear, at first I assume whatever column you retrieve is from main table and if all tables in query have same column name then use table_alias_name.column_name.

Upvotes: 1

Jens
Jens

Reputation: 69450

Try to do it this way:

SELECT w.wine_id, w.wine_name,  winery_name, region_name,  year, variety
FROM 
    wine AS w 
    join winery on w.winery_id = winery.winery_id
    join region on winery.region_id = region.region_id, 
    join grape_variety on grape_variety.variety_id = w.wine_id

Upvotes: 1

Manisha Patel
Manisha Patel

Reputation: 354

Use This one

$query = 
 "SELECT 
   w.wine_id, 
   w.wine_name, 
   winery.winery_name, 
   region.region_name, 
   year, 
   grape_variety.variety
FROM 
   wine AS w, 
   winery, 
   region, 
   grape_variety
JOIN 
   wine 
ON
   grape_variety.variety_id = wine.wine_id
JOIN 
   wine_variety 
ON
   wine.wine_id = wine_variety.variety_id
WHERE 
   winery.region_id = region.region_id
AND 
   wine.winery_id = winery.winery_id";
if (!empty($wineName)) {
$query .= " AND w.wine_name = '{$wineName}'";
}

if (!empty($wineryName)) {
$query .= " AND winery.winery_name = '{$wineryName}'";
}

// If the user has specified a region, add the regionName 
// as an AND clause
if (isset($regionName) && $regionName != "All") {
$query .= " AND region.region_name = '{$regionName}'"; 
} 

// If the user has specified a variety, add the grapeVariety 
// as an AND clause
if (isset($grapeVariety) && $grapeVariety != "Riesling") {
$query .= " AND grape_variety.variety = '{$grapeVariety}'"; 
 }

Upvotes: -1

Gabber
Gabber

Reputation: 7249

try this

SELECT 
    w.wine_id, 
    w.wine_name, 
    wy.winery_name, 
    r.region_name, 
    r.year, 
    wv.variety
FROM wine w 
INNER JOIN winery wy ON (wy.winery_id = w.winery_id )
INNER JOIN region r ON ( r.region_id = wy.region_id )
INNER JOIN  grape_variety gv ON (gv.variety_id = w.wine_id)
INNER JOIN  wine_variety wv ON  (wv.variety_id = w.wine_id)

Upvotes: 0

Related Questions