Professional Web Applications Themes

Distinct SELECT to query multiple table - MySQL

Hi have a database with two table. the first table caled dates has a set of codes in a column called tcode(s) in a column that is not unique to allow for repeating entries (yes tables are normalized). +----------------+----------------+--------------+ | code | startDate | endDate | +----------------+----------------+--------------+ |XYZ | 2005-06-07 |2005-10-11 | +----------------+----------------+--------------+ |XYZ | 2005-10-07 |2005-12-11 | +----------------+----------------+--------------+ |ZZZ | 2005-06-07 |2005-10-11 | +----------------+----------------+--------------+ i want to select only the distinct codes that match search criteria such as this: "SELECT DISTINCT code FROM dates WHERE startDate >= '2007-04-07' AND endDate <= '2005-10-11' The result is then something like XYZ,BBC,GED,PQR... ...

  1. #1

    Default Distinct SELECT to query multiple table

    Hi have a database with two table. the first table caled dates has a
    set of codes in a column called tcode(s) in a column that is not unique
    to allow for repeating entries (yes tables are normalized).
    +----------------+----------------+--------------+
    | code | startDate | endDate |
    +----------------+----------------+--------------+
    |XYZ | 2005-06-07 |2005-10-11 |
    +----------------+----------------+--------------+
    |XYZ | 2005-10-07 |2005-12-11 |
    +----------------+----------------+--------------+
    |ZZZ | 2005-06-07 |2005-10-11 |
    +----------------+----------------+--------------+
    i want to select only the distinct codes that match search criteria
    such as this:
    "SELECT DISTINCT code FROM dates WHERE startDate >= '2007-04-07' AND
    endDate <= '2005-10-11'
    The result is then something like
    XYZ,BBC,GED,PQR...
     
    +----------------+----------------+--------------+
    | code | name | price |
    +----------------+----------------+--------------+
    |XYZ | super-skii |200 |
    +----------------+----------------+--------------+
    |XYZ | desert sun |10 |
    +----------------+----------------+--------------+
    |ZZZ | chicken run |50 |
    +----------------+----------------+--------------+

    now ideally for one i could just do this for one code that isnt a
    problem:
    SELECT * FROM tripper WHERE price < 10 AND code LIKE XYZ

    So the question is how to go about it when there are multiple codes? an
    array? im working on a PHP MySQL solution for this one so if anyone has
    any ideas please dont hesitate would be much appreciated.

    thanks php_Boi

    php_Boi Guest

  2. #2

    Default Re: Distinct SELECT to query multiple table


    php_Boi wrote:
     
    > +----------------+----------------+--------------+
    > | code | name | price |
    > +----------------+----------------+--------------+
    > |XYZ | super-skii |200 |
    > +----------------+----------------+--------------+
    > |XYZ | desert sun |10 |
    > +----------------+----------------+--------------+
    > |ZZZ | chicken run |50 |
    > +----------------+----------------+--------------+
    >
    > now ideally for one i could just do this for one code that isnt a
    > problem:
    > SELECT * FROM tripper WHERE price < 10 AND code LIKE XYZ
    >
    > So the question is how to go about it when there are multiple codes? an
    > array? im working on a PHP MySQL solution for this one so if anyone has
    > any ideas please dont hesitate would be much appreciated.
    >
    > thanks php_Boi[/ref]
    I don't understand why you use code LINK XYZ rather than code = XYZ?

    Apart from that, it looks like what you need is a simple JOIN thus:

    SELECT `tripper`.*
    FROM `tripper`
    JOIN `dates` USING(`code`)
    WHERE `dates`.`price` < 10
    AND `tripper`.`startDate` >= '2007-04-07'
    AND `tripper`.`endDate` <= '2005-10-11'

    Captain Guest

  3. #3

    Default Re: Distinct SELECT to query multiple table

    php_Boi wrote:
     
    > +----------------+----------------+--------------+
    > | code | name | price |
    > +----------------+----------------+--------------+
    > |XYZ | super-skii |200 |
    > +----------------+----------------+--------------+
    > |XYZ | desert sun |10 |
    > +----------------+----------------+--------------+
    > |ZZZ | chicken run |50 |
    > +----------------+----------------+--------------+
    >
    > now ideally for one i could just do this for one code that isnt a
    > problem:
    > SELECT * FROM tripper WHERE price < 10 AND code LIKE XYZ
    >
    > So the question is how to go about it when there are multiple codes? an
    > array? im working on a PHP MySQL solution for this one so if anyone has
    > any ideas please dont hesitate would be much appreciated.
    >
    > thanks php_Boi[/ref]
    I don't understand why you use code LINK XYZ rather than code = XYZ?

    Apart from that, it looks like what you need is a simple JOIN thus:

    SELECT DISTINCT `tripper`.*
    FROM `tripper`
    JOIN `dates` USING(`code`)
    WHERE `dates`.`price` < 10
    AND `tripper`.`startDate` >= '2007-04-07'
    AND `tripper`.`endDate` <= '2005-10-11'

    Captain Guest

  4. #4

    Default Re: Distinct SELECT to query multiple table

    Yeah there we go SPOT on! thanks i realized what you said about the XYZ
    thing i meant to type = XYZ.

    Thanks a million Captain Paralytic

    Captain Paralytic wrote: 
    > > +----------------+----------------+--------------+
    > > | code | name | price |
    > > +----------------+----------------+--------------+
    > > |XYZ | super-skii |200 |
    > > +----------------+----------------+--------------+
    > > |XYZ | desert sun |10 |
    > > +----------------+----------------+--------------+
    > > |ZZZ | chicken run |50 |
    > > +----------------+----------------+--------------+
    > >
    > > now ideally for one i could just do this for one code that isnt a
    > > problem:
    > > SELECT * FROM tripper WHERE price < 10 AND code LIKE XYZ
    > >
    > > So the question is how to go about it when there are multiple codes? an
    > > array? im working on a PHP MySQL solution for this one so if anyone has
    > > any ideas please dont hesitate would be much appreciated.
    > >
    > > thanks php_Boi[/ref]
    > I don't understand why you use code LINK XYZ rather than code = XYZ?
    >
    > Apart from that, it looks like what you need is a simple JOIN thus:
    >
    > SELECT DISTINCT `tripper`.*
    > FROM `tripper`
    > JOIN `dates` USING(`code`)
    > WHERE `dates`.`price` < 10
    > AND `tripper`.`startDate` >= '2007-04-07'
    > AND `tripper`.`endDate` <= '2005-10-11'[/ref]

    php_Boi Guest

Similar Threads

  1. Replies: 8
    Last Post: November 15th, 07:08 PM
  2. (Oracle SQL) Select multiple columns from table
    By sinclairc5 in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: November 30th, 03:23 AM
  3. Insert into <table w/ text column> select distinct ...
    By Adam Nester in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 30th, 04:38 PM
  4. Replies: 3
    Last Post: April 18th, 12:52 PM
  5. Replies: 0
    Last Post: April 15th, 01:22 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