Conditional Joins

Hi all,

I have 4 tables with the structure shown below

Main Table :

Create Table TestMain
(TestMainId INT , TestCompanyID INT )

Other Tables :

Create Table TestCompany1
(Id INT , TestCompanyID INT )

Create Table TestCompany2
(Id INT , TestCompanyID INT )

Create Table TestCompany3
(Id INT , TestCompanyID INT )

In this above tables.. I would have a record in the table TestMain and a entry for that specific record would be in any of the tables like TestCompany1,TestCompany2,TestCompany3

Sample Records :

In the table TestMain

1 1000
2 2000
3 3000
4 4000
5 5000
6 6000
7 7000

In the table TestCompany1

1 1000
2 6000

In the table TestCompany2

1 3000
2 4000
3 5000

In the table TestCompany3

1 7000

How do I join those tables and fetch the main record with its subsequent entry from the other tables ?

Thanks in advance,

HHADo you have defined any relationship between the tables..? It's basic requirement for data integrity.

Anyway you can join the two tables this way...

Select Testmain.TestMainId, TestMain.TestCompanyID From TestMain
JOIN TestCompany1 ON TestMain.TestCompanyID = TestCompany1.TestCompanyID

You can join more than two tables using different join types...|||Hi

You would need to use left joins and maybe COALESCE but it is hard to know without more details. The fact that you are doing this hints that your design may not be sound too (although it may be - this looks like a mock up yes?).

HTH|||I think your best bet would be to inner join against each table and UNION or UNION ALL the results - your design does look dubious though, why are you segmenting companies across 3 tables - do they have different attributes per collection or is there another reason?|||Hi all,

There is a main table say COMPANY and they other tables CompanyA , CompanyB , CompanyC.

The main table COMPANY has the general info about the company ( like address , contact info) and there are 3 BIT columns to indicate what all type of company it falls under.If it falls under A & B , then the relevant information
are stored in CompanyA & CompanyB.

Now I need to write a proc which gets few input parameters and searches
for the company details.

1. If no parameters where passed , I need to get all the company from the
COMPANY with relevant information from the CompanyA, CompanyB,CompanyC.

2.If I get a parameter which says I should fetch only companys falling under
CompanyA , I should be able to get them too.

Still , the DB is in production , I cant touch the design.

Thanks for all your help ,

HHA|||http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71565

Terrible design - I'm sure you know or are at least becoming aware of.

Playing with post three from the link (not efficient - there may be better solutions):

CREATE TABLE #TESTMAIN
(TESTMAINID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY1
(ID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY2
(ID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY3
(ID INT , TESTCOMPANYID INT )

INSERT #TESTMAIN
SELECT 1, 1000 UNION ALL
SELECT 2, 2000 UNION ALL
SELECT 3, 3000 UNION ALL
SELECT 4, 4000 UNION ALL
SELECT 5, 5000 UNION ALL
SELECT 6, 6000 UNION ALL
SELECT 7, 7000

INSERT #TESTCOMPANY1
SELECT 1, 1000 UNION ALL
SELECT 2, 6000

INSERT #TESTCOMPANY2
SELECT 1, 3000 UNION ALL
SELECT 2, 4000 UNION ALL
SELECT 3, 5000

INSERT #TESTCOMPANY3
SELECT 1, 7000

DECLARE @.CompanyOneOnly AS Bit
SET @.CompanyOneOnly = 1

SELECT *
FROM -- Relvent companys
(SELECT X1.TESTMAINID,
X1.TESTCOMPANYID,
ISNULL(X2.ID,0) AS Comp1,
ISNULL(X3.ID,0) AS Comp2,
ISNULL(X4.ID,0) AS Comp3
FROM #TESTMAIN X1
LEFT JOIN #TESTCOMPANY1 X2 ON X1.TESTCOMPANYID = X2.TESTCOMPANYID
LEFT JOIN #TESTCOMPANY2 X3 ON X1.TESTCOMPANYID = X3.TESTCOMPANYID
LEFT JOIN #TESTCOMPANY3 X4 ON X1.TESTCOMPANYID = X4.TESTCOMPANYID) AS DerT
WHERE CAST(Comp1 AS Bit) = @.CompanyOneOnly OR @.CompanyOneOnly = 0

DROP TABLE #TESTMAIN
DROP TABLE #TESTCOMPANY1
DROP TABLE #TESTCOMPANY2
DROP TABLE #TESTCOMPANY3

Conditional Formatting in a Matrix Control

Hi there.

I am creating a report that the requirements need different background colors based on the row or column as shown below:

Community Total # of Respondents Resident's Overall Satisfaction Rating Quality of Repair May '07 41 3.6 5.0 April '07 14 1.8 3.0 Q2 '07 55 2.7 4.0 March '07 36 3.6 3.0 February '07 28 4.0 1.2 January '07 22 2.2 4.0 Q1 '07 86 3.3 2.7 YTD '07 141 3.0 3.2 December '06 33 3.8 4.2 November '06 27 2.6 5.0 October '06 42 1.8 3.0 Q4 '06 102 2.7 4.1 September '06 58 4.0 2.2 August '06 84 2.0 1.6 July '06 52 3.2 3.4 Q3 '06 194 3.1 2.4 June '06 40 2.4 4.2 May '06 41 3.6 5.0 April '06 14 1.8 3.0 Q2 '06 95 2.6 4.1 March '06 67 N/A 3.8 February '06 38 N/A 2.8 January '06 N/A 3.8 N/A Q1 '06 105 3.8 3.3 YTD '06 496 2.9 3.5 Rolling 12 Month Average 477 2.9 3.3

'

I can get the row colors to work great with an expression, but when I try to add the gray column with conditional formatting for the Resident's Overall Satisfaction Rating question, it clobbers my row formatting. I am thinking that I will have to do some gnarly expression in each of the rows and columns using the InScope function. Does that sound about right, or is there an easier way?

Thanks, Mike

Actually, this was easy once I looked at it again. On the detail cell, I just added another condition that identified the column in question and set the color appropriately. Works great.

Sometimes it just takes another look!

- Mike

Concatenation and NTEXT

Hi everyone,
I have a problem trying to update an NTEXT column by enclosing it
between two strings, as shown in the SQL below:
UPDATE MyTable SET NTextField = N'<pre>' + NTextField + N'</pre>'
The error I get back is this:
Invalid operator for data type. Operator equals add, type equals ntext.
Please help! I'm using SQL Server 2000.
Thanks,
JonoWhy would you want to update every column in the table with the exact same
enclosure? You can't concatenate to an NTEXT column, and I don't see any
value in doing it the exact same way for every row anyway.
Anyway, since we are talking about HTML, this is something you have the
presentation layer do. For example, it is easier in ASP to say:
<pre><%=rs("NTextField")%></pre>
...than to do what you are proposing. If you really want to do this, see
the UPDATETEXT function in Books Online, but I still recommend against the
approach.
"Jono" <jono.pare@.gmail.com> wrote in message
news:1143110984.030632.152630@.g10g2000cwb.googlegroups.com...
> Hi everyone,
> I have a problem trying to update an NTEXT column by enclosing it
> between two strings, as shown in the SQL below:
> UPDATE MyTable SET NTextField = N'<pre>' + NTextField + N'</pre>'
> The error I get back is this:
> Invalid operator for data type. Operator equals add, type equals ntext.
> Please help! I'm using SQL Server 2000.
> Thanks,
> Jono
>|||u need to use UPDATETEXT for this . Use the following example
-- CREATE TABLE TextExample (i int identity(1,1), text1 text, text2 text,
text3 text)
-- INSERT INTO TextExample SELECT REPLICATE('a',7998), REPLICATE('b',7998),
NULL
DECLARE @.txtPtr1 Varbinary(16)
DECLARE @.txtPtr2 Varbinary(16)
DECLARE @.txtPtr3 Varbinary(16)
SELECT @.txtPtr1 = TEXTPTR(text1)
FROM TextExample
SELECT @.txtPtr2 = TEXTPTR(text2)
FROM TextExample
UPDATE TextExample
SET Text3 = Text1
WHERE i = 1
SELECT @.txtPtr3 = TEXTPTR(text3)
FROM TextExample
WHERE i =1
SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1
UPDATETEXT TextExample.Text3 @.txtPtr3 NULL 0 ' '
SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1
UPDATETEXT TextExample.Text3 @.txtPtr3 NULL 0 TextExample.Text2 @.txtPtr2
SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1
"Jono" <jono.pare@.gmail.com> wrote in message
news:1143110984.030632.152630@.g10g2000cwb.googlegroups.com...
> Hi everyone,
> I have a problem trying to update an NTEXT column by enclosing it
> between two strings, as shown in the SQL below:
> UPDATE MyTable SET NTextField = N'<pre>' + NTextField + N'</pre>'
> The error I get back is this:
> Invalid operator for data type. Operator equals add, type equals ntext.
> Please help! I'm using SQL Server 2000.
> Thanks,
> Jono
>