Solved

SELECT FROM AS , using table alias

Posted on 2009-05-16
11
258 Views
Last Modified: 2013-12-12
Please help me apply the use of alias.

I grasp the concept of needing unique table or column identifiers.

here i have two tables.
in TABLE 1, is an id that is auto-increment by system with every new record entered, and this is PK.
in TABLE 2, is a user entered value that matches the  id in TABLE 1.

i want to pull all the records from both tables, and display some columns from each table in one dataset

SELECT FROM WHERE always gives the 'you need an alias' error.
So far all my efforts with SELECT FROM AS return a syntax error

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 'on notes.notesid AS notes.id=RD_Product_Application.raid AS RD_Product_Applicati' at line 2

your considerate review and ideas are welcome!
thanks
kind regards,
amy
$q6="select lab_notes, application

    from  notes, RD_Product_Application inner join on notes.notesid AS notes.id=RD_Product_Application.raid AS RD_Product_Application.id";

 

 $result = mysql_query( $q6, $db )

or die(" - Failed More Information:<br><pre>$q6</pre><br>Error: " . mysql_error());
 

$num_rows = mysql_num_rows($result);

if ($myrow = mysql_fetch_array($result)) {
 

echo "<br>select lab_notes, application

    from  notes, RD_Product_application inner join on notes.notesid AS notes.id=RD_Product_Application.raid AS RD_PRoduct_Application.id<BR><br>";

echo "this query throws errors";

echo "<table border=1>\n";

echo "<tr><td><b>Ref ID</b></td><td><b>lab notes</b></td><td>Chem ID</td><td>Product Num</td><td>Application Info</td></tr>\n";
 

do {

printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n", $myrow["no.id"], $myrow["lab_notes"], $myrow["chem_id"], $myrow["RD_Prod_Num"], $myrow["application"]);

} while ($myrow = mysql_fetch_array($result));

echo "</table>\n";

} 

?>

Open in new window

0
Comment
Question by:yamya
  • 5
  • 4
  • 2
11 Comments
 
LVL 14

Expert Comment

by:racek
Comment Utility

select lab_notes, application

from  notes n 

JOIN RD_Product_application r on n.notesid = r.raid ;

Open in new window

0
 

Author Comment

by:yamya
Comment Utility
Racek,
Thank you that worked nicely.

I am getting an "ambiguous' error when trying to printf <td>%s</td> $myrow['id'].

I've tried
$myrow['notes.id']
$myrow['n.id']

I tried revising the query you provided as
SELECT table1  r (some fields),  table2  n (more fields) from table2 n join table1 r on n.fieldid=r.fieldid

so far no luck.

any more tricks up your sleeve?
thanks
amy
0
 
LVL 4

Expert Comment

by:TMarkham1
Comment Utility

The SELECT statement should follow this format:

SELECT column1,
column2
FROM table1 as tb1
INNER JOIN table2 as tb2
ON tb1.column1 = tb2.column1

If you are having problems with ambiguous columns, you will need to give the column names an alias as well. In other words, in my example above, if both table1 and table2 had a column named column1 you will need to do something like the following.

SELECT tb1.column1 as "tb1col1",
              tb2.column1 as "tb2col1",
              tb1.column2
FROM table1 as tb1
INNER JOIN table2 as tb2
ON tb1.column1 = tb2.column1
0
 

Author Comment

by:yamya
Comment Utility
I have clearly missed this lesson, try as I will to follow the logic.

select table1.column1 and table2.column (hereafter known as tb1c1and tb2c1, and are being renamed because they have the same column names).
then.
along with table1.colum2 (which doesn't get renamed),
take from table1 (now known as tb1) and hook up with table2 (now known as tb2)
and find every record with a matching value in tb1.tb1c1 and tb2.tb2c1.

I can't for the life of me figure out what happens to table1.column2 in the above statement.


here is the last bit that I tried, with no success. i've tried a number of variations to the naming of things.

wish there was an easier way to see this picture!
thanks for your patient help

regards,
amy

<?php

$db = mysql_connect("host", "user", "pw");

mysql_select_db("joe",$db) or die ('Unable to connect to database');
 

$query13="SELECT RD_Product_Application.id as "prodid",

              notes.id as "noteid",

              RD_Product_Application.chem_id

FROM RD_Product_Application as RDpa

INNER JOIN notes as nt

ON RDpa.prodid = nt.noteid";
 

$result = mysql_query( $query13, $db )

or die(" - Failed More Information:<br><pre>$q13</pre><br>Error: " . mysql_error());
 

$num_rows = mysql_num_rows($result);

if ($myrow = mysql_fetch_array($result)) {
 
 

echo "SELECT RD_Product_Application.id as ".$prodid."" 

              .$noteid " as noteid";

			  

             echo " RD_Product_Application.chem_id

FROM RD_Product_Application as RDpa

INNER JOIN notes as nt

ON RD_Product_Application.id = notes.id";
 

echo "hello and welcome to the notes section for " .$RDpa." ";

?>

Open in new window

0
 
LVL 4

Expert Comment

by:TMarkham1
Comment Utility
You're closer than you think! Ttry changing your code to the following snippet.

Please understand that I know SQL... but I'm not sure about PHP syntax, so I'm relying on you for that.

Basically, I changed your last line below. The columns in the JOIN statement are still referenced by their original names, not their alias.


$query13="SELECT RD_Product_Application.id as "prodid",

              notes.id as "noteid",

              RD_Product_Application.chem_id

FROM RD_Product_Application as RDpa

INNER JOIN notes as nt

ON RDpa.id = nt.id";

Open in new window

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 4

Expert Comment

by:TMarkham1
Comment Utility
More specifically, your query could look like this...
SELECT RDpa.id as "prodid",

       nt.id as "noteid",

       RDpa.chem_id

FROM RD_Product_Application as RDpa

INNER JOIN notes as nt

ON RDpa.id = nt.id

Open in new window

0
 

Author Comment

by:yamya
Comment Utility
that query returns a distinct list of values from RDpa.chem_id.

what is required to make other column values display??
 i've tried a couple different things but i can only get one column of values to appear.

seems from all i read in books or online, you either 'get it' or you don't! haha - i want to get it, i really do!

thanks for your continued input.

0
 
LVL 14

Expert Comment

by:racek
Comment Utility
your question
SELECT table1  r (some fields),  table2  n (more fields) from table2 n join table1 r on n.fieldid=r.fieldid
is wrong

1. start with TABLE ALIASES in FROM
.. FROM table1 AS a, table2 AS b

2. continue with connection between tables (JOIN conitions)
WHERE a.id = b.id

3. then COLUMN ALIASES in SELECT
select a.column1 AS xxx, a.column2 AS yyy,, b.id AS bid ....
0
 

Author Comment

by:yamya
Comment Utility
i thought that made complete sense until i tried it out.
now i get the same error, no matter what ; Error: Unknown column 'prod.id' in 'field list'

here is what i did and the error just changes the problem column depending on what i do with the table alias ..

Here are my three ALIAS tables[columns]
PRODUCT aka RD_Product_Application [id (pk) , chem_id, application, RD_Prod_Num]
NOTE  aka notes[id (pk) , lab_notes, RD_Prod_Num_id]
FAMILY aka chemistry [id (pk), chem_name]

the relationships are:
RD_Product_Application.RD_Prod_Num=notes.RD_Prod_Num_id
RD_Product_Application.chem_id=chemistry.id

I want to show all the PRODUCT records that have an associated NOTE record and I would like the FAMILY name to appear rather than the chem_id

I can get each of these pieces alone, but cannot stitch them together . I'll live without the FAMILY name if I have to.

thanks much!
amy


$q2="SELECT NOTE.id as a,

       PRODUCT.id as b,

       NOTE.lab_notes

FROM notes as NOTE

INNER JOIN notes as n

ON PRODUCT.id = NOTE.id";

Open in new window

0
 
LVL 4

Accepted Solution

by:
TMarkham1 earned 250 total points
Comment Utility
OK, let's try this:

Assuming your real tables names are RD_Product_Application, notes, and chemistry... and that the column names you mention above are the actual column names, the following query should give you what you are looking for. At this point, all it is showing the RD_Prod_Num from the RD_Product_Application table and the chem_name from the chemistry table is there is one.

An INNER JOIN basically means, "bring back all records from RD_Product_Application where there is a matching record in notes". The LEFT OUTER JOIN below says, "show me all records from RD_Product_Application regardless if there is a matching record in chemistry... but if there are matching records in chemistry, bring them back too." The LEFT OUTER JOIN basically will not stop records in RD_Product_Application from being if they don't have a matching record in chemistry.

Notice that I am not using any column aliases... only table aliases. The table aliases I chose were:

p for RD_Product_Application
n for notes
c for chemistry

You could add column aliases if you wanted to. If you don't, you will end up two columns in your result set named RD_Prod_Num and chem_name.
 
SELECT p.RD_Prod_Num,

       c.chem_name

FROM RD_Product_Application AS p

INNER JOIN notes AS n

ON p.RD_Prod_Num = n.RD_Prod_Num_id

LEFT OUTER JOIN chemistry AS c

ON p.chem_id = c.id

Open in new window

0
 

Author Comment

by:yamya
Comment Utility
Nothing like a typo to kill a few hours - guess we all know THAT!
that cleared up much of the issue - the return was incomplete because in the printf clause, $myrow was actually typed $myrows for one column.

Meanwhile, I'm having some success with some code i found on another site with another tutorial. I can accomplish most of my needs by SELECTING all and then just displaying what I want. maybe someday the database will be so large and then that might be a problem with speed I suppose.

Thanks for all the help. I'm good to go for now.

Cheerio!
Amy




$q3="SELECT * FROM RD_Product_Application as r LEFT JOIN notes as n on r.id=n.id ORDER BY n.id ASC";

Open in new window

0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now