Join Statement

--001517576e8eff4846047ab2bb7d
Content-Type: text/plain; charset=ISO-8859-1

Hi;
I have the following:

mysql> select SKU, Quantity, Name, Price, p.sizes,
p.colorsShadesNumbersShort from tem126080739853 t join products p on
t.ProdID-p.ID;
Empty set (0.00 sec)

mysql> select * from tem126080739853;
+----+--------+----------+-------------+-------------------- ------+
| ID | ProdID | Quantity | sizes | colorsShadesNumbersShort |
+----+--------+----------+-------------+-------------------- ------+
| 1 | 2 | 2 | Extra-small | navy-blue:CC7722 |
+----+--------+----------+-------------+-------------------- ------+
1 row in set (0.00 sec)
mysql> select SKU, Quantity, Name, Price, p.sizes,
p.colorsShadesNumbersShort from tem126080739853 t join products p on
t.ProdID-p.ID;
Empty set (0.03 sec)

mysql> select SKU, Quantity, Name, Price, t.sizes,
t.colorsShadesNumbersShort from tem126080739853 t join products p on
t.ProdID-p.ID;
Empty set (0.00 sec)

mysql> select ID, SKU, Name, Price from products;
+----+----------+-------+--------+
| ID | SKU | Name | Price |
+----+----------+-------+--------+
| 2 | prodSKU1 | name1 | 555.22 |
+----+----------+-------+--------+
1 row in set (0.00 sec)

So I'm at a loss as to why the above select join statement fails. Please
advise.
TIA,
Victor

--001517576e8eff4846047ab2bb7d--

Re: Join Statement

--------------040609050200010403070705
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

> ... on t.ProdID-p.ID;

Your join clause subtracts the two IDs, so it's on IDs that differ, and
apparently there aren't any.

PB

-----

Victor Subervi wrote:
> Hi;
> I have the following:
>
> mysql> select SKU, Quantity, Name, Price, p.sizes,
> p.colorsShadesNumbersShort from tem126080739853 t join products p on
> t.ProdID-p.ID;
> Empty set (0.00 sec)
>
> mysql> select * from tem126080739853;
> +----+--------+----------+-------------+-------------------- ------+
> | ID | ProdID | Quantity | sizes | colorsShadesNumbersShort |
> +----+--------+----------+-------------+-------------------- ------+
> | 1 | 2 | 2 | Extra-small | navy-blue:CC7722 |
> +----+--------+----------+-------------+-------------------- ------+
> 1 row in set (0.00 sec)
> mysql> select SKU, Quantity, Name, Price, p.sizes,
> p.colorsShadesNumbersShort from tem126080739853 t join products p on
> t.ProdID-p.ID;
> Empty set (0.03 sec)
>
> mysql> select SKU, Quantity, Name, Price, t.sizes,
> t.colorsShadesNumbersShort from tem126080739853 t join products p on
> t.ProdID-p.ID;
> Empty set (0.00 sec)
>
> mysql> select ID, SKU, Name, Price from products;
> +----+----------+-------+--------+
> | ID | SKU | Name | Price |
> +----+----------+-------+--------+
> | 2 | prodSKU1 | name1 | 555.22 |
> +----+----------+-------+--------+
> 1 row in set (0.00 sec)
>
> So I'm at a loss as to why the above select join statement fails. Please
> advise.
> TIA,
> Victor
>
>
> ------------------------------------------------------------ ------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.427 / Virus Database: 270.14.107/2564 - Release Date: 12/14/09 07:37:00
>
>

--------------040609050200010403070705--

Re: Join Statement

--00163683425862f708047ab2fd69
Content-Type: text/plain; charset=ISO-8859-1

On Mon, Dec 14, 2009 at 12:37 PM, Peter Brawley > wrote:

> > ... on t.ProdID-p.ID;
>
> Your join clause subtracts the two IDs, so it's on IDs that differ, and
> apparently there aren't any.
>

I beg to differ:

mysql> select SKU, Quantity, Name, Price, p.sizes,
p.colorsShadesNumbersShort from tem126080739853 t join products p on
t.ProdID-p.ID ;
Empty set (0.00 sec)

mysql> select * from tem126080739853;
+----+--------+----------+----
---------+--------------------------+
| ID | ProdID | Quantity | sizes | colorsShadesNumbersShort |
+----+--------+----------+-------------+-------------------- ------+
| 1 | 2 | 2 | Extra-small | navy-blue:CC7722 |
+----+--------+----------+-------------+-------------------- ------+
1 row in set (0.00 sec)
mysql> select SKU, Quantity, Name, Price, p.sizes,
p.colorsShadesNumbersShort from tem126080739853 t join products p on
t.ProdID-p.ID ;
Empty set (0.03 sec)

mysql> select SKU, Quantity, Name, Price, t.sizes,
t.colorsShadesNumbersShort from tem126080739853 t join products p on
t.ProdID-p.ID ;
Empty set (0.00 sec)

mysql> select ID, SKU, Name, Price from products;
+----+----------+-------+--------+
| ID | SKU | Name | Price |
+----+----------+-------+--------+
| 2 | prodSKU1 | name1 | 555.22 |
+----+----------+-------+--------+
1 row in set (0.00 sec)

t.ProdID == 2
p.ID == 2
That's a match.
So why does my select join fail?
TIA,
V

--00163683425862f708047ab2fd69--

Re: Join Statement

Victor Subervi wrote:
> On Mon, Dec 14, 2009 at 12:37 PM, Peter Brawley >> wrote:
>
>> > ... on t.ProdID-p.ID;
>>
>> Your join clause subtracts the two IDs, so it's on IDs that differ, and
>> apparently there aren't any.
>>
>
> I beg to differ:
>
> ...
> So why does my select join fail?
> TIA,
> V
>

It fails because you aren't using = but are using - . Switch your query to

"... ON t.ProdID = p.ID"

and see if that works any better.



--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Join Statement

Victor Subervi wrote:
> On Mon, Dec 14, 2009 a
>
> mysql> select SKU, Quantity, Name, Price, t.sizes,
> t.colorsShadesNumbersShort from tem126080739853 t join products p on
> t.ProdID-p.ID ;
> Empty set (0.00 sec)
>
> mysql> select ID, SKU, Name, Price from products;
> +----+----------+-------+--------+
> | ID | SKU | Name | Price |
> +----+----------+-------+--------+
> | 2 | prodSKU1 | name1 | 555.22 |
> +----+----------+-------+--------+
> 1 row in set (0.00 sec)
>
> t.ProdID == 2
> p.ID == 2
> That's a match.
> So why does my select join fail?

Because you're using a minus sign where you should be using an equals
sign. This is what you're doing:

select
SKU, Quantity, Name, Price, t.sizes,t.colorsShadesNumbersShort
from tem126080739853 t
join products p on
t.ProdID-p.ID

This is what you should be doing:

select
SKU, Quantity, Name, Price, t.sizes,t.colorsShadesNumbersShort
from tem126080739853 t
join products p on
t.ProdID=p.ID

Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Join Statement

Victor,

As far as I can see... change the '-' to '='.

<-- fixed query -->
SELECT
SKU
, Quantity
, Name
, Price
, p.sizes
, p.colorsShadesNumbersShort
FROM
tem126080739853 t JOIN products p ON
t.ProdID = p.ID
;
<-- end -->

I didn't run this, or try to replicate it, it was just my observation on the
query.


Steven Staples


-----Original Message-----
From: Victor Subervi [mailto:victorsubervi@gmail.com]
Sent: December 14, 2009 11:26 AM
To: mysql@lists.mysql.com
Subject: Join Statement

Hi;
I have the following:

mysql> select SKU, Quantity, Name, Price, p.sizes,
p.colorsShadesNumbersShort from tem126080739853 t join products p on
t.ProdID-p.ID;
Empty set (0.00 sec)

mysql> select * from tem126080739853;
+----+--------+----------+-------------+-------------------- ------+
| ID | ProdID | Quantity | sizes | colorsShadesNumbersShort |
+----+--------+----------+-------------+-------------------- ------+
| 1 | 2 | 2 | Extra-small | navy-blue:CC7722 |
+----+--------+----------+-------------+-------------------- ------+
1 row in set (0.00 sec)
mysql> select SKU, Quantity, Name, Price, p.sizes,
p.colorsShadesNumbersShort from tem126080739853 t join products p on
t.ProdID-p.ID;
Empty set (0.03 sec)

mysql> select SKU, Quantity, Name, Price, t.sizes,
t.colorsShadesNumbersShort from tem126080739853 t join products p on
t.ProdID-p.ID;
Empty set (0.00 sec)

mysql> select ID, SKU, Name, Price from products;
+----+----------+-------+--------+
| ID | SKU | Name | Price |
+----+----------+-------+--------+
| 2 | prodSKU1 | name1 | 555.22 |
+----+----------+-------+--------+
1 row in set (0.00 sec)

So I'm at a loss as to why the above select join statement fails. Please
advise.
TIA,
Victor

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.716 / Virus Database: 270.14.101/2555 - Release Date: 12/14/09
02:37:00


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Join Statement

--------------030106050203000902000306
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Victor,

> I beg to differ:
> mysql> select SKU, Quantity, Name, Price, p.sizes,
> p.colorsShadesNumbersShort from tem126080739853 t join products p on
> t.ProdID-p.ID ;

....on t.ProdID-p.ID... _subtracts_ the two IDs. To match them use '='
rather than '-'.

PB

-----

Victor Subervi wrote:
> On Mon, Dec 14, 2009 at 12:37 PM, Peter Brawley >
>> wrote:
>>
>
>
>> > ... on t.ProdID-p.ID;
>>
>> Your join clause subtracts the two IDs, so it's on IDs that differ, and
>> apparently there aren't any.
>>
>>
>
> I beg to differ:
>
> mysql> select SKU, Quantity, Name, Price, p.sizes,
> p.colorsShadesNumbersShort from tem126080739853 t join products p on
> t.ProdID-p.ID ;
> Empty set (0.00 sec)
>
> mysql> select * from tem126080739853;
> +----+--------+----------+----
> ---------+--------------------------+
> | ID | ProdID | Quantity | sizes | colorsShadesNumbersShort |
> +----+--------+----------+-------------+-------------------- ------+
> | 1 | 2 | 2 | Extra-small | navy-blue:CC7722 |
> +----+--------+----------+-------------+-------------------- ------+
> 1 row in set (0.00 sec)
> mysql> select SKU, Quantity, Name, Price, p.sizes,
> p.colorsShadesNumbersShort from tem126080739853 t join products p on
> t.ProdID-p.ID ;
> Empty set (0.03 sec)
>
> mysql> select SKU, Quantity, Name, Price, t.sizes,
> t.colorsShadesNumbersShort from tem126080739853 t join products p on
> t.ProdID-p.ID ;
> Empty set (0.00 sec)
>
> mysql> select ID, SKU, Name, Price from products;
> +----+----------+-------+--------+
> | ID | SKU | Name | Price |
> +----+----------+-------+--------+
> | 2 | prodSKU1 | name1 | 555.22 |
> +----+----------+-------+--------+
> 1 row in set (0.00 sec)
>
> t.ProdID == 2
> p.ID == 2
> That's a match.
> So why does my select join fail?
> TIA,
> V
>
>
> ------------------------------------------------------------ ------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.427 / Virus Database: 270.14.107/2564 - Release Date: 12/14/09 07:37:00
>
>

--------------030106050203000902000306--

Re: Join Statement

--002354471014c6786d047ab3e725
Content-Type: text/plain; charset=ISO-8859-1

On Mon, Dec 14, 2009 at 1:04 PM, Peter Brawley
wrote:

> ...on t.ProdID-p.ID... _subtracts_ the two IDs. To match them use '='
> rather than '-'.
>

Thank you for all of you that caught that.
V

--002354471014c6786d047ab3e725--