Join Query in mssql 2005

Question

0.00/5 (No votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

i have 3 table

1.Chemical
--------------------------------------------------
Id	chemical
7	Aluminium Metal Foil
8	Aluminium Metal Powder
9	Iodine
------------------------------------------------

2.Stock
------------------------------------------------
Id	Date	    PONo	ChemicalId	Grade	   QuantityA	QuantityB	Unit
1	1/10/2013 QL/PO/1303001	8	             GR	       1	1000	         gm
2	1/10/2013 QL/PO/1303001	9	               GR	1	1000	          gm
----------------------------------------------------------------------------------------------

3.Consumed
----------------------------------------------------------
Id	date	ChemicalId	Grade	Quantity	units
1	3/2/2013 0:00	8	GR	200	gm
2	3/5/2013 0:00	8	GR	200	gm
3	3/1/2013 0:00	9	GR	300	gm
4	3/4/2013 0:00	9	GR	300	gm

----------------------------------------------------------------

1st table is chemical master 2nd one is chemicalstock when i purchase the chemical add to stock and 3rd table is consumption

i want a query which out like stock register

Date	   chemical	Units	Stock	Consumed
1/10/2013 	8	gm	1000	-
3/2/2013 	8	gm	-	200
3/5/2013 	8	gm	-	300
------------------------------------------------

plz help.

Posted 5-Mar-13 1:19am

Nikhil Bhivgade

Updated 5-Mar-13 1:32am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v2

Add a Solution

Comments

gvprabu 5-Mar-13 7:34am

You need to list only one Chemical or all chemicals at a time?

Nikhil Bhivgade 6-Mar-13 3:07am

all chemical

gvprabu 6-Mar-13 3:34am

I updated the answer.... It will solve ur problem. Try that and get back to me

Nikhil Bhivgade 6-Mar-13 5:15am

thanks...i will get back 2 u..if any problem occurs..

Nikhil Bhivgade 6-Mar-13 6:21am

i m giving u table structure
-------------------------------------------
CREATE TABLE [dbo].[Consumption](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ConsumeDate] [smalldatetime] NULL,
[ChemicalId] [int] NULL,
[Grade] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Quantity] [decimal](18, 0) NULL,
[Unit] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
------------------------------------------------------------------
CREATE TABLE [dbo].[Recevied](
[Id] [int] IDENTITY(1,1) NOT NULL,
[RecivedDate] [smalldatetime] NULL,
[PONO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ChallanNo] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ChemicalId] [int] NULL,
[Grade] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[QuantityA] [decimal](18, 0) NULL,
[QuantityB] [decimal](18, 0) NULL,
[Units] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ManufacturerName] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Items] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

3 solutions

Solution 2

Try this one

SQL

SELECT t.Date,
       t.ChemicalId                AS Chemical,
       t.Unit,
       ISNULL(t.StockQty, '-')     AS Stock,
       ISNULL(t.ConsumedQty, '-')  AS Consumed
FROM   (
           SELECT DISTINCT s.Date,
                  ChemicalId,
                  Unit,
                  CAST(Quantity AS VARCHAR)  AS StockQty
           FROM   Stock s
                  INNER JOIN (
                           SELECT DISTINCT c.Date,
                                  ChemicalId,
                                  Unit,
                                  CAST(Quantity AS VARCHAR) AS ConsumedQty
                           FROM   Consumed c
                       )    AS t2
                       ON  t1.ChemicalId = t2.ChemicalId
                       AND t.Date = t2.Date
       )                           AS t

Posted 5-Mar-13 23:35pm

Khorshed Alam, Dhaka

Comments

Nikhil Bhivgade 6-Mar-13 6:51am

i have given table structure plz have a look.

Solution 3

i had used this query

SQL

SELECT     Date,chemical, Unit, Stock, Consumed
FROM         (SELECT     RecivedDate AS 'Date', ChemicalId AS 'chemical', Units as 'Unit',CAST(QuantityB AS VARCHAR)  AS    'Stock', '-' AS 'Consumed'
                       FROM          Recevied
                       UNION ALL
                       SELECT     ConsumeDate AS 'Date', ChemicalId AS 'chemical', Unit, '-' AS 'Stock', CAST(Quantity AS VARCHAR) AS 'Consumed'
                       FROM         Consumption ) AS T

thanks 2 all for ur answer , i wont be able to get it.

Posted 6-Mar-13 1:31am

Nikhil Bhivgade

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Accepted Answer · 2013-03-05T01:33:00

Solution 1

Hi

Try this...

SQL

SELECT T.Date, T.chemical, T.Unit, T.Stock, T.Consumed
FROM (SELECT Date, ChemicalId 'chemical', Unit, CAST(QuantityB AS VARCHAR(15)) 'Stock', '-' 'Consumed', 1 'DispOrder'
      FROM Stock
      UNION ALL 
      SELECT date, ChemicalId 'chemical', units, '-' 'Stock', CAST(Quantity AS VARCHAR(15)) 'Consumed', 2 'DispOrder'
      FROM Consumed) T 
ORDER BY T.chemical, T.DispOrder, T.DateC

Regards,
GVPrabu

Posted 5-Mar-13 1:33am

gvprabu

Updated 6-Mar-13 0:15am

v4

Comments

Nikhil Bhivgade 5-Mar-13 7:37am

thier can be n number of chemical like 8,9,10------ n then i need to group by them

gvprabu 5-Mar-13 7:42am

Check my post

Nikhil Bhivgade 6-Mar-13 6:13am

Arithmetic overflow error converting varchar to data type numeric.

gvprabu 6-Mar-13 6:16am

This is because of UNION ALL Query... I update Check now