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: [email protected] 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]
