Professional Web Applications Themes

MySQL subquery in select - MySQL

Hey NG, I got a tabel like this: +----+-------+--------+ | id | value | type | +----+-------+--------+ | 1 | 512 | art no | +----+-------+--------+ | 2 | 32 | size | +----+-------+--------+ And i would like to get a result like this: +--------+--------+ | art_no | size | +--------+--------+ | 512 | 32 | +--------+--------+ I can't do subselections, like i can in MS SQL - can anyone solve my problem? Kind regards, Kasper K....

  1. #1

    Default MySQL subquery in select

    Hey NG,

    I got a tabel like this:

    +----+-------+--------+
    | id | value | type |
    +----+-------+--------+
    | 1 | 512 | art no |
    +----+-------+--------+
    | 2 | 32 | size |
    +----+-------+--------+


    And i would like to get a result like this:

    +--------+--------+
    | art_no | size |
    +--------+--------+
    | 512 | 32 |
    +--------+--------+


    I can't do subselections, like i can in MS SQL - can anyone solve my
    problem?


    Kind regards,
    Kasper K.
    Kasper K Guest

  2. #2

    Default Re: MySQL subquery in select

    Kasper K wrote:
    > | id | value | type |
    > +----+-------+--------+
    > | 1 | 512 | art no |
    > | 2 | 32 | size |
    > And i would like to get a result like this:
    > | art_no | size |
    > +--------+--------+
    > | 512 | 32 |
    > I can't do subselections, like i can in MS SQL - can anyone solve my
    > problem?
    Write how you would do that in T-SQL. Maybe that will help someone get a better
    idea of what you want.
    Walter Vaughan Guest

  3. #3

    Default Re: MySQL subquery in select

    Walter Vaughan skrev:
    > Write how you would do that in T-SQL. Maybe that will help someone get a
    > better idea of what you want.
    Hey Walter,

    T-SQL? im not familiar with that?
    The thing i want though is - if you take a look at my first table you
    see that i have a col named: value, and another col named: type.
    Now i want to split my value col out in one row. And let the new tabel
    field titels become my type col text.

    Is it still hard to understand? I explain the best i can - and i really
    need your help!

    Im using MySQL 4.1

    Kind regards,
    Kasper K.
    Kasper K Guest

  4. #4

    Default Re: MySQL subquery in select

    Kasper K wrote:
    > T-SQL? im not familiar with that?
    T-SQL is Microsoft's version of SQL. If you would write what you want in
    Microsoft's version of SQL, someone here might be able to come up with a work
    around for MySQL.

    --
    Walter
    Walter Vaughan Guest

  5. #5

    Default Re: MySQL subquery in select

    Walter Vaughan skrev:
    > T-SQL is Microsoft's version of SQL. If you would write what you want in
    > Microsoft's version of SQL, someone here might be able to come up with a
    > work around for MySQL.
    It wouyld be something like this:

    persons : ID, NAME
    weddings: ID, MANID, WOMANID, DATE

    SELECT DATE,
    (SELECT NAME FROM PERSONS WHERE persons.ID=wedding.MANID) AS MANNAME,
    (SELECT NAME FROM PERSONS WHERE persons.ID=wedding.WOMANID) AS WOMANNAME
    FROM WEDDINGS

    Result:

    DATE MANNAME WOMANNAME
    2000-01-01 John Mary
    2000-01-05 Johnny Betty


    A query where i can make more than one select and where statement..
    I hope you are able to help me.
    Kasper K Guest

  6. #6

    Default Re: MySQL subquery in select

    Kasper K wrote:
    > Walter Vaughan skrev:
    >> T-SQL is Microsoft's version of SQL. If you would write what you
    >> want in Microsoft's version of SQL, someone here might be able to
    >> come up with a work around for MySQL.
    >
    > It wouyld be something like this:
    >
    > persons : ID, NAME
    > weddings: ID, MANID, WOMANID, DATE
    >
    > SELECT DATE,
    > (SELECT NAME FROM PERSONS WHERE persons.ID=wedding.MANID) AS MANNAME,
    > (SELECT NAME FROM PERSONS WHERE persons.ID=wedding.WOMANID) AS
    > WOMANNAME FROM WEDDINGS
    >
    > Result:
    >
    > DATE MANNAME WOMANNAME
    > 2000-01-01 John Mary
    > 2000-01-05 Johnny Betty
    >
    >
    > A query where i can make more than one select and where statement..
    > I hope you are able to help me.
    SELECT weddings.Date, persons1.Name AS manname, persons2.name AS womanname
    FROM `weddings`
    JOIN persons AS persons1 ON weddings.manid = persons1.id
    JOIN persons AS persons2 ON weddings.womanid = persons2.id


    Paul Lautman Guest

  7. #7

    Default Re: MySQL subquery in select

    Paul Lautman skrev:
    > SELECT weddings.Date, persons1.Name AS manname, persons2.name AS womanname
    > FROM `weddings`
    > JOIN persons AS persons1 ON weddings.manid = persons1.id
    > JOIN persons AS persons2 ON weddings.womanid = persons2.id

    You are the best Paul!
    I myself came up with an slightly other way to do it. I'll paste it in
    later. In case anybody should care.

    Kasper K.
    Kasper K Guest

Similar Threads

  1. Replies: 0
    Last Post: July 23rd, 12:15 AM
  2. mySQL subquery problem
    By rmorgan in forum Coldfusion Database Access
    Replies: 7
    Last Post: October 30th, 07:06 AM
  3. Mysql select query with php
    By Benjamin Dickgießer in forum PHP Development
    Replies: 7
    Last Post: November 20th, 10:57 PM
  4. MySQL Q - SELECT with 2 tables
    By Geoff Berrow in forum PHP Development
    Replies: 4
    Last Post: September 5th, 07:17 AM
  5. Select From SubQuery
    By SriSamp in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 09:28 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139