How do we make this query work in 3.23.51

How do we make this query work in 3.23.51

Post by Arul » Wed, 03 Jul 2002 19:02:40



Hi

I had written a query which executes in MySql 4.0.1
But this is not working in 3.23.51 coz i had used Union function in the
query which is not supported by 3.23.51

So any idea how this can be made to work on 3.23.51. I need to have only one
Column in the result as TYPENAME

SELECT
             CT.Company_Type as TYPENAME
FROM
             RFQ_Details RD, Company_Type CT
WHERE
             RD.RFQID = 4
            AND  RD.SEARCH_Type = 'COMPANY'
            AND Company_TypeID = RD.TypeID

UNION

SELECT
             UT.USER_Type as TYPENAME
FROM
             RFQ_Details RD, User_Type UT
WHERE
             RD.RFQID = 4
            AND RD.SEARCH_Type = 'USER'
            AND User_TypeID = RD.TypeID

---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)



Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

 
 
 

How do we make this query work in 3.23.51

Post by Oswald, Andrea » Wed, 03 Jul 2002 19:04:40


Hi Arul,

maybe copying the columns into a temporary table might do the
job. I don't know how big your tables are, if performance might
be a problem.

The following statements should create the query you wanted:

/*creating temporary table*/
CREATE TABLE tmp_Information
(
  TYPENAME (any typ-definition varchar,int???)
);

/*inserting rows for company information*/
INSERT INTO tmp_Information
SELECT CT.Company_Type as TYPENAME
FROM   RFQ_Details RD, Company_Type CT
WHERE  RD.RFQID = 4 AND  RD.SEARCH_Type = 'COMPANY'
       AND Company_TypeID = RD.TypeID;

/*inserting rows for user information*/
INSERT INTO tmp_Information
SELECT  UT.USER_Type as TYPENAME
FROM    RFQ_Details RD, User_Type UT
WHERE   RD.RFQID = 4 AND RD.SEARCH_Type = 'USER'
        AND User_TypeID = RD.TypeID;

/*do whatever you want with the rows of the union*/
SELECT TYPENAME FROM tmp_Information;

/*erase the temporary table*/
DROP TABLE tmp_Information

Hopes that helps.

Take care

Andreas

> -----Ursprngliche Nachricht-----

> Gesendet: Dienstag, 2. Juli 2002 11:04
> An: MySQL
> Betreff: How do we make this query work in 3.23.51

> Hi

> I had written a query which executes in MySql 4.0.1
> But this is not working in 3.23.51 coz i had used Union
> function in the
> query which is not supported by 3.23.51

> So any idea how this can be made to work on 3.23.51. I need
> to have only one
> Column in the result as TYPENAME

> SELECT
>              CT.Company_Type as TYPENAME
> FROM
>              RFQ_Details RD, Company_Type CT
> WHERE
>              RD.RFQID = 4
>             AND  RD.SEARCH_Type = 'COMPANY'
>             AND Company_TypeID = RD.TypeID

> UNION

> SELECT
>              UT.USER_Type as TYPENAME
> FROM
>              RFQ_Details RD, User_Type UT
> WHERE
>              RD.RFQID = 4
>             AND RD.SEARCH_Type = 'USER'
>             AND User_TypeID = RD.TypeID

---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)



Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

 
 
 

How do we make this query work in 3.23.51

Post by Arul » Wed, 03 Jul 2002 21:42:43


Yea Andreas

I had that idea but i thought that would be a performance issue...
So i thought of avoiding this temp table...
I am looking into an option where i can do it in 1 single query...

-Arul

----- Original Message -----



Sent: Tuesday, July 02, 2002 3:09 PM
Subject: AW: How do we make this query work in 3.23.51

> Hi Arul,

> maybe copying the columns into a temporary table might do the
> job. I don't know how big your tables are, if performance might
> be a problem.

> The following statements should create the query you wanted:

> /*creating temporary table*/
> CREATE TABLE tmp_Information
> (
>   TYPENAME (any typ-definition varchar,int???)
> );

> /*inserting rows for company information*/
> INSERT INTO tmp_Information
> SELECT CT.Company_Type as TYPENAME
> FROM   RFQ_Details RD, Company_Type CT
> WHERE  RD.RFQID = 4 AND  RD.SEARCH_Type = 'COMPANY'
>        AND Company_TypeID = RD.TypeID;

> /*inserting rows for user information*/
> INSERT INTO tmp_Information
> SELECT  UT.USER_Type as TYPENAME
> FROM    RFQ_Details RD, User_Type UT
> WHERE   RD.RFQID = 4 AND RD.SEARCH_Type = 'USER'
>         AND User_TypeID = RD.TypeID;

> /*do whatever you want with the rows of the union*/
> SELECT TYPENAME FROM tmp_Information;

> /*erase the temporary table*/
> DROP TABLE tmp_Information

> Hopes that helps.

> Take care

> Andreas

> > -----Ursprngliche Nachricht-----

> > Gesendet: Dienstag, 2. Juli 2002 11:04
> > An: MySQL
> > Betreff: How do we make this query work in 3.23.51

> > Hi

> > I had written a query which executes in MySql 4.0.1
> > But this is not working in 3.23.51 coz i had used Union
> > function in the
> > query which is not supported by 3.23.51

> > So any idea how this can be made to work on 3.23.51. I need
> > to have only one
> > Column in the result as TYPENAME

> > SELECT
> >              CT.Company_Type as TYPENAME
> > FROM
> >              RFQ_Details RD, Company_Type CT
> > WHERE
> >              RD.RFQID = 4
> >             AND  RD.SEARCH_Type = 'COMPANY'
> >             AND Company_TypeID = RD.TypeID

> > UNION

> > SELECT
> >              UT.USER_Type as TYPENAME
> > FROM
> >              RFQ_Details RD, User_Type UT
> > WHERE
> >              RD.RFQID = 4
> >             AND RD.SEARCH_Type = 'USER'
> >             AND User_TypeID = RD.TypeID

> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)


> To unsubscribe, e-mail

> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)



Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php