Solved

SELECT FROM AS , using table alias

Posted on 2009-05-16
11
272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 14

Expert Comment

by:racek
ID: 24404246

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
ID: 24404321
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
ID: 24404341

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
Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

 

Author Comment

by:yamya
ID: 24404835
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
ID: 24404893
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
 
LVL 4

Expert Comment

by:TMarkham1
ID: 24404908
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
ID: 24405058
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
ID: 24405061
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
ID: 24405149
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
ID: 24405236
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
ID: 24405288
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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 count occurrences of each item in an array.

617 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