Rhino
This is great it works a treat Thanks Regards John Berman -----Original Message----- From: Rhino [mailto:[EMAIL PROTECTED] Sent: 29 February 2004 13:40 To: [EMAIL PROTECTED]; 'Paul DuBois' Cc: [EMAIL PROTECTED] Subject: Re: Query Help I hope you don't mind me butting in but your note was sent to the whole group.... The technique Paul is describing involves adding another expression to each of the SELECT clauses in the UNION. Up to now, you've probably just used column names or functions in a SELECT clause, for example: select dept, avg(salary) from employee where job <> 'Manager' group by dept; (NOTE: I'm using the term 'select clause' to mean just the part of the Select statement that precedes the 'from' clause. In other words, the first line of the above query as opposed to the whole query.) It is also possible to include other expressions, like strings, in your queries. For example: select dept, avg(salary), 'extra expression' from employee where job <> 'Manager' group by dept; That query will have exactly the same result as the previous example (assuming no INSERTs, DELETEs, or UPDATEs to the table in the meantime!) except that each of the result rows will include a third column. In each case, the value in the third column will be the string 'extra expression'. If you apply that technique to your union, you can use it to determine which table was the source of each of the rows in your result set. For example: select dept, avg(salary), 'Manager' from employee where job = 'Manager' group by dept UNION select dept, avg(salary), 'Non-Manager' from employee where job <> 'Manager' group by dept; The result set of this query will contain three columns, the third of which will have 'Manager' in all of the rows contributed by the first query and 'Non-Manager' in all of the rows contributed by the second query. I'll leave you to apply this concept to *your* query ;-) Rhino ----- Original Message ----- From: "John Berman" <[EMAIL PROTECTED]> To: "'Paul DuBois'" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Sunday, February 29, 2004 3:54 AM Subject: RE: Query Help > Paul > > > Sorry to be a pain. I'm not sure that I understand > > Select an extra column in each SELECT. SELECT "member", ... UNION > SELECT "non-member", ... > > > Regards > > John Berman > > -----Original Message----- > From: Paul DuBois [mailto:[EMAIL PROTECTED] > Sent: 29 February 2004 03:11 > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: RE: Query Help > > At 2:45 +0000 2/29/04, John Berman wrote: > >Got it working at last > > > >SELECT lists_.DescShort_ FROM lists_ WHERE (((lists_.Name_) Not In (select > >members_.List_ from members_ where members_.EmailAddr_ like ('"& em & > >"')))) union SELECT lists_.DescShort_ FROM members_ INNER JOIN lists_ ON > >members_.List_ = lists_.Name_ WHERE (members_.EmailAddr_ = ('"& em & "')) > > > >My only problem being it now lists the lists Im not a member of and the > ones > >I am a member of - how on earth do I show on screen which is which ? > > Maybe: > > Select an extra column in each SELECT. SELECT "member", ... UNION > SELECT "non-member", ... > > > -- > Paul DuBois, MySQL Documentation Team > Madison, Wisconsin, USA > MySQL AB, www.mysql.com > > MySQL Users Conference: April 14-16, 2004 > http://www.mysql.com/uc2004/ > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
