List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 1 1999 3:03pm
Subject:Re: Help with Query pls...subquery possible?
View as plain text  
On Wed, 1999-09-01 12:50:24 +0000, Matt Duggan wrote:
[query re-formatted]

 SELECT ctag
      , grp
      , l_activity
      , problem
      , o_tech
      , l_tech
      , sid,human_sid
      , o_date
      , (UNIX_TIMESTAMP(NOW())-o_date)/3600
 FROM slips
 WHERE ((UNIX_TIMESTAMP(NOW())-o_date) >= VAR1*3600)
   AND status!=2
 ORDER BY sid DESC;

> This works, substitute var1 by the amount of hours you want and away it
> goes...
> 
> However, the 'grp' field is a numerical value that maps to a table
> containing group names,
> 'ctag' is similar, it's a numerical id that maps to a table
> containing customer information.
> 
> What I need to do is somehow do a lookup on those tables at the same
> time, [...]

This is the typical join functionality: combine rows of tables tied
together by some common field.

Just do this:

 SELECT c.customername /* value from customertable  */
      , g.grpname      /* value from groupnametable */
      , s.l_activity
      , s.problem
      , s.o_tech
      , s.l_tech
      , s.sid,human_sid
      , s.o_date
      , (UNIX_TIMESTAMP(NOW())-s.o_date)/3600
 FROM slips AS s
    , groupnametable AS g
    , customertable  AS c
 WHERE ((UNIX_TIMESTAMP(NOW())-s.o_date) >= VAR1*3600)
   AND s.status!=2
   AND s.ctag = c.ctag  /* this joins the corresponding rows from s and c */
   AND s.grp  = g.grp   /* this joins the corresponding rows from s and g */
 ORDER BY s.sid DESC;

If there are no identical field names in s, g, and c, one _might_
leave out the table prefixes and just write "o_date" instead of
"s.o_date", but I think it's better style to always use table
prefixes, as it helps to avoid errors (especially if the table schemes
are going to change ...).

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Help with Query pls...subquery possible?Matt Duggan1 Sep
  • Re: Help with Query pls...subquery possible?Martin Ramsch1 Sep