List:General Discussion« Previous MessageNext Message »
From:Kerry Frater Date:February 26 2007 7:44am
Subject:RE: SELECT single row from 2 tables with WHERE clause
View as plain text  
Many thanks Peter. That's the definition I was after.

Kerry
  -----Original Message-----
  From: Peter K AGANYO [mailto:kimaiga@stripped]On Behalf Of Peter K AGANYO
  Sent: 19 February 2007 00:35
  To: kerry@stripped
  Cc: mysql@stripped
  Subject: Re: SELECT single row from 2 tables with WHERE clause


  Hi Kerry,

  Try this:

  SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1 LEFT JOIN table2
AS t2 ON t1.linkfield=t2.linkfield WHERE t1.lookup='ZZZZZ'

  Without the WHERE condition this would return all 1000 rows of table 1
since "A Left join returns all rows of the left of the conditional even if
there is no right column to match." but t1.lookup='ZZZZZ' constrains this to
only the one row of table one with lookup equal to 'ZZZZZ'.

  Enjoy

  Peter


  On 2/17/07, Kerry Frater <kerry@stripped> wrote:
    I am trying to select a particular row from a table and include a column
for
    aq second table but I cannot get the result I am after.

    I have "table1" with 1000 rows and "table2" with 12 rows. The
relationship
    between the tables is a column "linkedfield". Table1 has a unique key
called
    "lookup"

    If I use the code
    SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2
    where (t1.lookup='ZZZZZ')
    I get a result of 12 rows (as expected)

    SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2
    where (t1.linkfield=t2.linkfield)
    I get 1000 rows as expected

    SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2
    where (t1.lookup='ZZZZZ') and (t1.linkfield=t2.linkfield)
    returns 1 row if there is an active link between the two tables and

    returns 0 rows if there isn't.

    This is where I am stuck. In the last example I would like the 1 row
whether
    there is an "active" link or not. The difference will be simply that the
    "t2desc" rsulting column will be blank or contain a value.

    Can anyone help me with the logic?

    Kerry

Thread
#2002 - The server is not respondingPeter Bradley15 Feb
  • Re: #2002 - The server is not respondingOnur15 Feb
    • Re: #2002 - The server is not respondingPeter Bradley16 Feb
      • Re: #2002 - The server is not respondingPeter Bradley16 Feb
      • SELECT single row from 2 tables with WHERE clauseKerry Frater17 Feb
        • Re: SELECT single row from 2 tables with WHERE clauseBrian Mansell19 Feb
RE: SELECT single row from 2 tables with WHERE clauseKerry Frater26 Feb