Distinct SELECT to query multiple table

**Distinct SELECT to query multiple table -** 06-04-2007, 07:48 AM

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...

>From there i have a second table called tripper
+----------------+----------------+--------------+
| 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

**Re: Distinct SELECT to query multiple table -** 06-04-2007, 07:48 AM

php_Boi wrote:

> 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...
>
> >From there i have a second table called tripper
> +----------------+----------------+--------------+
> | 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
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'

**Re: Distinct SELECT to query multiple table -** 06-04-2007, 07:48 AM

php_Boi wrote:

> 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...
>
> >From there i have a second table called tripper
> +----------------+----------------+--------------+
> | 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
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'

**Re: Distinct SELECT to query multiple table -** 06-04-2007, 07:48 AM

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:
> php_Boi wrote:
>
> > 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...
> >
> > >From there i have a second table called tripper
> > +----------------+----------------+--------------+
> > | 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
> 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'