Professional Web Applications Themes

joins(?) - MySQL

Hi, I have a question about joining tables and i cannot figure it out. Hope somebody can help me: I have two tables table1 ------------------- fieldid | shortlist ------------------- name | y adress | n city | n email | y table2 ---------------------- docid | fieldid | data ---------------------- 1 | name | hans 2 | name | piet 2 | email | nl Is it possible te create the following result: result --------------- id | fieldid | data 1 | name | hans 1 | email | NULL 2 | name | piet 2 | email | nl so, ...

  1. #1

    Default joins(?)

    Hi,

    I have a question about joining tables and i cannot figure it out.
    Hope somebody can help me:

    I have two tables

    table1
    -------------------
    fieldid | shortlist
    -------------------
    name | y
    adress | n
    city | n
    email | y


    table2
    ----------------------
    docid | fieldid | data
    ----------------------
    1 | name | hans
    2 | name | piet
    2 | email | nl


    Is it possible te create the following result:

    result
    ---------------
    id | fieldid | data
    1 | name | hans
    1 | email | NULL
    2 | name | piet
    2 | email | nl


    so, for every field where table1.shortlist = 'y' want to have a
    corresponding table2.data. if it isn't there then i want a NULL value

    Thanks,
    eHannes

    eHannes Guest

  2. #2

    Default Re: joins(?)

    eHannes wrote: 


    i think this'll do it. run this baby like this

    select
    docid,
    fieldid,
    data

    from table2,
    table1

    where title = fieldid
    and shortlist = 'y'


    --
    lark -- net
    To reply to me directly, delete "despam".
    lark Guest

  3. #3

    Default Re: joins(?)

    eHannes <nl> wrote in news:1178028671.060641.174050
    y80g2000hsf.googlegroups.com:
     

    look into "left join"s.

    Good Guest

  4. #4

    Default Re: joins(?)

    On 1 mei, 16:48, Good Man <com> wrote: 
    >
    > look into "left join"s.[/ref]

    That's exactly what i tried (and a right one too) but with no results.


    eHannes Guest

  5. #5

    Default Re: joins(?)

    eHannes <nl> wrote in news:1178086607.011057.169060
    u30g2000hsc.googlegroups.com:
     
    >>
    >> look into "left join"s.[/ref]
    >
    > That's exactly what i tried (and a right one too) but with no results.[/ref]

    then you should look at how you've designed your tables. i understood the
    result you *wanted* to get, but your table design made no sense to me. why
    is there a column called "data" that is holding a first name, a last name,
    and an email address in different rows???

    Good Guest

  6. #6

    Default Re: joins(?)

    On Wed, 02 May 2007 10:17:44 -0500, Good Man wrote: 
    >>
    >> That's exactly what i tried (and a right one too) but with no results.[/ref]
    >
    > then you should look at how you've designed your tables. i understood the
    > result you *wanted* to get, but your table design made no sense to me. why
    > is there a column called "data" that is holding a first name, a last name,
    > and an email address in different rows???[/ref]

    It may or may not be a good design for this particular thing, but as a
    general case, this kind of technique serves a very real purpose: It
    accomodates not knowing completely what the database will be used for at
    the time that the design and programming are finalized. Changes to
    what data are stored don't require code changes. Plus, it's amazingly
    useful for storing dynamic structured objects, so long as the actual
    structure somewhat resembles

    CREATE TABLE `trashheap` (
    `rowid` int(11) NOT NULL auto_increment,
    `parent_rowid` int(11) default NULL,
    `label` varchar(250) NOT NULL,
    `data` varchar(250) default NULL,
    PRIMARY KEY (`rowid`),
    KEY `FK_trashheap` (`parent_rowid`),
    CONSTRAINT `trashheap_ibfk_1` FOREIGN KEY (`parent_rowid`)
    REFERENCES `trashheap` (`rowid`)
    ) ENGINE=InnoDB

    --
    12. One of my advisors will be an average five-year-old child. Any flaws in my
    plan that he is able to spot will be corrected before implementation.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  7. #7

    Default Re: joins(?)

    "Peter H. Coffin" <com> wrote in
    news:ninehells.com:
     
    >
    > It may or may not be a good design for this particular thing, but as a
    > general case, this kind of technique serves a very real purpose: It
    > accomodates not knowing completely what the database will be used for
    > at the time that the design and programming are finalized.[/ref]

    Thanks for the insight Peter, I can see how that would be useful!
    Good Guest

Similar Threads

  1. JOINS
    By InkFasT! in forum Coldfusion Database Access
    Replies: 4
    Last Post: July 3rd, 05:28 PM
  2. More than two INNER JOINS
    By Manuel in forum Coldfusion - Getting Started
    Replies: 4
    Last Post: June 19th, 12:51 AM
  3. JOINs instead of AND
    By Jim Michaels in forum MySQL
    Replies: 1
    Last Post: March 11th, 02:26 AM
  4. SQL help on joins
    By zCrow in forum Dreamweaver AppDev
    Replies: 3
    Last Post: May 10th, 09:17 PM

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