Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SELECT FROM AS , using table alias

Posted on 2009-05-16
11
Medium Priority
?
274 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
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

876 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