From: Peter Brawley
Date: February 11 2006 9:27pm
Subject: Re: How to select data if not in both tables?
List-Archive: http://lists.mysql.com/mysql/194805
Message-Id: <43EE56AE.5040809@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=======AVGMAIL-43EE56AE185F======="
--=======AVGMAIL-43EE56AE185F=======
Content-Type: multipart/alternative; boundary=------------080409060908060205010509
--------------080409060908060205010509
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Grant,
>If I want to select all the products that are in the product_table,
>but not in the sale_table, how to make the query? The product_table
>has all the products, but the sale table is a subset of the product_table.
SELECT * FROM product_table p
LEFT JOIN sale_table s USING (prod_id)
WHERE s.prod_id IS NULL;
PB
Grant Giddens wrote:
> Hi,
>
> I have 2 tables like:
>
> product_table:
> prod_id
> item_name
> price
> data
> data
> etc
>
> sale_table:
> prod_id
> sale_price
>
> If I want to select all the products that are in the product_table, but not in the sale_table, how to make the query? The product_table has all the products, but the sale table is a subset of the product_table.
>
> I tried:
>
> SELECT item_name FROM product_table, sale_table WHERE product_table.prod_id != sale_table.prod_id but I'm not getting any results from that.
>
> Sorry this code isn't exact, I'm not att my development PC at the moment.
>
> Thanks,
> Grant
>
>
> ---------------------------------
> Brings words and photos together (easily) with
> PhotoMail - it's free and works with Yahoo! Mail.
>
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 267.15.6/257 - Release Date: 2/10/2006
>
--------------080409060908060205010509
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Grant,
>If I want to select all the products that are in the product_table,
>but not in the sale_table, how to make the query? The product_table
>has all the products, but the sale table is a subset of the product_table.
SELECT * FROM product_table p
LEFT JOIN sale_table s USING (prod_id)
WHERE s.prod_id IS NULL;
PB
Grant Giddens wrote:
Hi,
I have 2 tables like:
product_table:
prod_id
item_name
price
data
data
etc
sale_table:
prod_id
sale_price
If I want to select all the products that are in the product_table, but not in the sale_table, how to make the query? The product_table has all the products, but the sale table is a subset of the product_table.
I tried:
SELECT item_name FROM product_table, sale_table WHERE product_table.prod_id != sale_table.prod_id but I'm not getting any results from that.
Sorry this code isn't exact, I'm not att my development PC at the moment.
Thanks,
Grant
---------------------------------
Brings words and photos together (easily) with
PhotoMail - it's free and works with Yahoo! Mail.
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.6/257 - Release Date: 2/10/2006
--------------080409060908060205010509--
--=======AVGMAIL-43EE56AE185F=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.6/257 - Release Date: 2/10/2006
--=======AVGMAIL-43EE56AE185F=======--