We want to return all the branches that has sold copies of either of the books in either of the periods. WITH <MyCTEName> (BRANCHES) makes it easy to get a starting point for the main select:

FROM Branches B
LEFT JOIN Book_Details BD1A ON B.BranchCode = ...
LEFT JOIN Book_Details BD1B ON B.BranchCode = ...
LEFT JOIN Book_Details BD2A ON B.BranchCode = ...
LEFT JOIN Book_Details BD2B ON B.BranchCode = ...

Without the WITH, we wouldn't have anything to put in the WHERE clause - or rather, I think it would be possible with three UNIONs, but the SQL would be long (I'd guess about 100 lines), whereas the SQL I suggested is about 25 lines. Below, I've tried to outline partially how I'd expect this SQL to look, but frankly, it confuses me and I by far prefer a simple WITH statement. Alternatively, I suppose you could use FULL JOIN rather than LEFT JOIN. That way, you wouldn't have any UNIONS, but each table would have to be ORed to all others (well, you could probably use COALESCE) within the FULL JOIN. That would look neater than using UNIONs (outlined below), but in my opinion it is more complex than using WITH. Though this could just be due to me not ever having used FULL JOIN.

Set

1)
SELECT ...
FROM Book_Details BD1A
LEFT JOIN Book_Details BD1AB1 ON BD1A.Branch_Code = ...
LEFT JOIN Book_Details BD2AA1 ON BD1A.Branch_Code = ...
LEFT JOIN Book_Details BD2AB1 ON BD1A.Branch_Code = ...
...
UNION
SELECT ...
FROM Book_Details BD1B
LEFT JOIN Book_Details BD1BA2 ON BD1B.Branch_Code = ...
LEFT JOIN Book_Details BD2BA2 ON BD1B.Branch_Code = ...
LEFT JOIN Book_Details BD2BB2 ON BD1B.Branch_Code = ...
WHERE BD1BA2.Branch_Code is NULL
...
UNION
SELECT ...
FROM Book_Details BD2A
LEFT JOIN Book_Details BD1AA3 ON BD2A.Branch_Code = ...
LEFT JOIN Book_Details BD1AB3 ON BD2A.Branch_Code = ...
LEFT JOIN Book_Details BD2AB3 ON BD2A.Branch_Code = ...
WHERE BD1AA3.Branch_Code is NULL
AND BD1AB3.Branch_Code is NULL
...

2)
SELECT COALESCE(BD1A.Branch_Code, BD1B.Branch_Code, BD2A.Branch_Code, BD2B.Branch_Code) AS Branch_Code, ...
FROM Book_Details BD1A
FULL JOIN Book_Details BD1B ON BD1A.Branch_Code = BD1B.Branch_Code
...
FULL JOIN Book_Details BD2A ON (BD1A.Branch_Code = BD2A.Branch_Code
OR BD1B.Branch_Code = BD2A.Branch_Code)
...
FULL JOIN Book_Details BD2B ON (BD1A.Branch_Code = BD2B.Branch_Code
OR BD1B.Branch_Code = BD2B.Branch_Code
OR BD2A.Branch_Code = BD2B.Branch_Code)
...

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Vishal Tiwari
Sent: 8. september 2010 06:21
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Re: Data in Row Format, Need To Display In Columun Format

Hi Dear SET (A Real Master-Trouble Shooter),


Just give me some time, I hope I may do it with your suggested sql.

Sorry I have one doubt, you gave me following code, please let me know, what it is for ?

WITH Branches(BranchCode) as

(SELECT DISTINCT sBrachCode

FROM Book_Details

WHERE (sYearMonth = :YearMonthA or sYearMonth = :YearMonthB)

AND BookName in(:MyFirstBookName, :MySecondBookName)

)


Thank You Very Much And With Best Regards.

Vishal

--- On Wed, 8/9/10, Svein Erling <svein.erling.tysvaer@...> wrote:

From: Svein Erling <svein.erling.tysvaer@...>
Subject: [firebird-support] Re: Data in Row Format, Need To Display In Columun Format
To: firebird-support@yahoogroups.com
Date: Wednesday, 8 September, 2010, 3:34 AM







 









--- In firebird-support@yahoogroups.com, Vishal Tiwari <vishualsoft@...> wrote:

>

> Dear Set (A Real Master-Trouble Shooter),

>  

> Let me ask you one question, if I know the name of those books for which I need to print those respective columns then is it easy to write sql ?

>  

> I am ready to get books name at application level (i.e. coding level) and if required I would make the required sql using loop in my application and the this looped sql I would merge (concatinate) with original "Select ..." SQL.

>  

> Is it possible, or if you have any suggestion by which I could get data using only sql, or any this I need to do at application level i.e. any thing required to do at application level and finaly I make the sql, would I get such logic ?

>  

Sure, if you know the name of the books, things are simpler and you can even name the columns as you want (I've only named three fields, but this part is a no-brainer if you can get the name from an application before creating the SQL statement). I assume you're only interested in branches actually having sold the books below:



WITH Branches(BranchCode) as

(SELECT DISTINCT sBrachCode

FROM Book_Details

WHERE (sYearMonth = :YearMonthA or sYearMonth = :YearMonthB)

AND BookName in(:MyFirstBookName, :MySecondBookName)

)



SELECT B.BranchCode, BD1A.rBook_Price as Book1PriceYearMonthA, BD1B.rBook_Price as Book1PriceYearMonthB,

coalesce(BD1A.iCopy_Sold, 0) - coalesce(BD1B.iCopy_Sold, 0) as Book1CopySoldDifference,

coalesce(BD1A.Tot_Amount, 0) - coalesce(BD1B.Tot_Amount, 0),

BD2A.rBook_Price, BD2B.rBook_Price, coalesce(BD2A.iCopy_Sold, 0) -

coalesce(BD2B.iCopy_Sold, 0),

coalesce(BD2A.Tot_Amount, 0) - coalesce(BD2B.Tot_Amount, 0)

FROM Branches B

LEFT JOIN Book_Details BD1A ON B.BranchCode = BD1A.BranchCode

AND BD1A.BookName = :MyFirstBookName

AND BD1A.sYearMonth = :YearMonthA

LEFT JOIN Book_Details BD1B ON B.BranchCode = BD1B.BranchCode

AND BD1B.BookName = :MyFirstBookName

AND BD1B.sYearMonth = :YearMonthB

LEFT JOIN Book_Details BD2A ON B.BranchCode = BD2A.BranchCode

AND BD2A.BookName = :MySecondBookName

AND BD2A.sYearMonth = :YearMonthA

LEFT JOIN Book_Details BD2B ON B.BranchCode = BD2B.BranchCode

AND BD2B.BookName = :MySecondBookName

AND BD2B.sYearMonth = :YearMonthB



HTH,

Set
























[Non-text portions of this message have been removed]



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

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links