drop table if exists A;
drop table if exists B;
create table A (
item varchar(30),
primary key (item) );
create table B (
item varchar(30),
price decimal(7,2) not null default 0.0,
primary key (item),
unique key (item) );
insert into A values ('Book'),('Pencil'),('Table');
insert into B values ('Book',11.95),('Pencil',2.20);
--
-- Show Contents of A and B
--
select * from A;
select * from B;
--
-- This gives you Items with no price
--
select A.item from A left join B on A.item=B.item where B.item is null;
--
-- This gives you Items with a price
--
select A.item from A left join B on A.item=B.item where B.item is not null;
--
-- This gives you all Items and a price if one exists.
-- Otherwise, 0.0. is the displayed price
--
select A.item,IFNULL(B.price,0.00) price
from A left join B on A.item=B.item;
Give it a try !!!
----- Original Message -----
From: Renish <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Sent: Sunday, November 26, 2006 10:15:43 PM GMT-0500 US/Eastern
Subject: MySql doubt.
Hi Guys,
I have an easy doubt..
Table A consist of field Item and Table B consist of field Price. Table A
consists of Items which has price aswell the items which doesnt have price.
But In Table B,I have kept only the item which has price . Now, I want to
subtract the total items in Table A to the total items in Table B. so I get
the items which doesnt have price. I used union its not working.. can any of
u guys write the code for it,,,
For example
Table A
Item Book
Pencil
Table
Table B- which consists of Item with price
Price -Book-11 dollar
Pencil-2 dollar
Please reply me asap. I hope it should be v. easy enough to tackle.
Cheers,
Renish koshy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
