Do I need a Join? if so How...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Do I need a Join? if so How...

Author

Topic

micrapip
Starting Member

11 Posts

Posted - 2008-12-04 : 12:18:22

This is a continuation of the other thread but more indepth...

I have 2 tables (AMDB and SCORE) each have very similar fields. In using the posts in the other thread I have been successfull at running the following statements against each table individualy:

SELECT [BTR Transit list],
COUNT(CASE WHEN [Category_class]='PRINTER' THEN [Category_class] ELSE NULL END) AS Printer,
COUNT(CASE WHEN [Category_class]='Desktop/Tower' THEN [Category_class] ELSE NULL END)AS Desktop,
COUNT(CASE WHEN [Category_class]='APTM' THEN [Category_class] ELSE NULL END) as PinPad,
COUNT(CASE WHEN [Category_class]='Monitor' THEN [Category_class] ELSE NULL END)as Monitor,
COUNT(CASE WHEN [Category_class]='Laptop' THEN [Category_class] ELSE NULL END)as Laptop,
COUNT(CASE WHEN [Category_class]='Network' THEN [Category_class] ELSE NULL END)as Network
FROM AMDB where [Flex9] = 'RFSI08-032' or [Flex9] = 'RFS!08-032' or [Flex9] = 'RFSI08023' or [Flex9] = 'RFSI08032' or [Flex9] = 'RFSI-0832' or [Flex9] = 'RFSI-0832' or [Flex9] = 'RFSI8-032' or [Flex9] = 'N/A'
GROUP BY [BTR Transit list]order by [BTR Transit list]

SELECT [Transit],
COUNT(CASE WHEN [Category]='Printer' THEN [Category] ELSE NULL END) AS Printer,
COUNT(CASE WHEN [Category]='Desktop' THEN [Category] ELSE NULL END) AS Desktop,
COUNT(CASE WHEN [Category]='APTM' THEN [Category] ELSE NULL END) AS PinPad,
COUNT(CASE WHEN [Category]='Monitor' THEN [Category] ELSE NULL END) AS Monitor,
COUNT(CASE WHEN [Category]='Laptop' THEN [Category] ELSE NULL END) AS Laptop,
COUNT(CASE WHEN [Category]='Network' THEN [Category] ELSE NULL END) AS Network
FROM SCORE
GROUP BY [Transit]order by [Transit]

With great results

But what I'm now looking for is the following output...

Transit|AMDB Desktop|Score Desktop|AMDB Printer|Score Printer| etc..

But I need 2 flavours:

1 where all crierion match Based on Count

i.e.AMDB.Desktop=SCORE.desktop and AMDB.Printer=SCORE.Printer etc.

2. where criterion does not match.

I hope this isn't to criptic if it is let me know and I will explain further

Thanks A million again.. Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 12:23:34

[code]
SELECT Transit,AMDB.Desktop,Score.Desktop,AMDB.Printer,Score.Printer,...
FROM (1st query)AMDB
JOIN (2nd query)Score
ON Score.[Transit]=AMDB.[BTR Transit list]
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 12:24:09

not sure what you meant by two flavours. can you explain that part?

micrapip
Starting Member

11 Posts

Posted - 2008-12-04 : 16:19:54

Sorry,

The first one I need the output where the Transit and all of the equipment counts are = between AMDB and SCORE tables

The second one is the out put where the count is not equal to each other...

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-04 : 18:44:08

May be this one from Visakh's query:

quote:

Originally posted by visakh16


----match
SELECT Transit,AMDB.Desktop,Score.Desktop,AMDB.Printer,Score.Printer,...
FROM (1st query)AMDB
JOIN (2nd query)Score
ON Score.[Transit]=AMDB.[BTR Transit list]
and AMDB.Desktop=SCORE.desktop and AMDB.Printer = SCORE.Printer

------non match
SELECT Transit,AMDB.Desktop,Score.Desktop,AMDB.Printer,Score.Printer,...
FROM (1st query)AMDB
JOIN (2nd query)Score
ON Score.[Transit]=AMDB.[BTR Transit list]
and AMDB.Desktop <> SCORE.desktop and AMDB.Printer <>SCORE.Printer

micrapip
Starting Member

11 Posts

Posted - 2008-12-05 : 11:16:42

greetings,

one more question, for

SELECT transit,AMDB.Desktop,Score.Desktop,AMDB.Printer,Score.Printer,...
FROM (1st query)AMDB
JOIN (2nd query)Score
ON Score.[Transit]=AMDB.[BTR Transit list]
and AMDB.Desktop=SCORE.desktop and AMDB.Printer = SCORE.Printer

(1st query)/(2nd query) what do I put in there? I have saved each query as AMDB_List.sql and SCORE_List.sql, do i need to add the full path?

Sorry about this

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-05 : 11:22:25

Put your above query in Ist and 2nd

quote:
Originally posted by micrapip

greetings,

one more question, for

SELECT transit,AMDB.Desktop,Score.Desktop,AMDB.Printer,Score.Printer,...
FROM (1st query)AMDB
JOIN (2nd query)Score
ON Score.[Transit]=AMDB.[BTR Transit list]
and AMDB.Desktop=SCORE.desktop and AMDB.Printer = SCORE.Printer

(1st query)/(2nd query) what do I put in there? I have saved each query as AMDB_List.sql and SCORE_List.sql, do i need to add the full path?

Sorry about this

micrapip
Starting Member

11 Posts

Posted - 2008-12-05 : 11:23:50

and....

for SELECT Transit,AMDB.Desktop,Score.Desktop,AMDB.Printer,Score.Printer,...
FROM (1st query)AMDB
JOIN (2nd query)Score
ON Score.[Transit]=AMDB.[BTR Transit list]
and AMDB.Desktop <> SCORE.desktop and AMDB.Printer <>SCORE.Printer

Should I use OR because desktops might match but printers might not? what I need is if ANY does not match to be displayed

micrapip
Starting Member

11 Posts

Posted - 2008-12-05 : 11:35:31

k here is what I did for the Match

SELECT [BTR Transit list],AMDB_Master.Desktop,Score.Desktop,AMDB_Master.Printer,Score.Printer,AMDB_Master.PinPad,Score.PinPad,AMDB_Master.Monitor,Score.Monitor,AMDB_Master.Laptop,Score.Laptop,AMDB_Master.Network,Score.Network,
FROM (SELECT [BTR Transit list],
COUNT(CASE WHEN [Category_class]='PRINTER' THEN [Category_class] ELSE NULL END) AS Printer,
COUNT(CASE WHEN [Category_class]='Desktop/Tower' THEN [Category_class] ELSE NULL END)AS Desktop,
COUNT(CASE WHEN [Category_class]='APTM' THEN [Category_class] ELSE NULL END) as PinPad,
COUNT(CASE WHEN [Category_class]='Monitor' THEN [Category_class] ELSE NULL END)as Monitor,
COUNT(CASE WHEN [Category_class]='Laptop' THEN [Category_class] ELSE NULL END)as Laptop,
COUNT(CASE WHEN [Category_class]='Network' THEN [Category_class] ELSE NULL END)as Network
FROM AMDB_Master where [Flex9] = 'RFSI08-032' or [Flex9] = 'RFS!08-032' or [Flex9] = 'RFSI08023' or [Flex9] = 'RFSI08032' or [Flex9] = 'RFSI-0832' or [Flex9] = 'RFSI-0832' or [Flex9] = 'RFSI8-032' or [Flex9] = 'N/A'
)AMDB_Master
JOIN (SELECT [Transit],
COUNT(CASE WHEN [Category]='Printer' THEN [Category] ELSE NULL END) AS Printer,
COUNT(CASE WHEN [Category]='Desktop' THEN [Category] ELSE NULL END) AS Desktop,
COUNT(CASE WHEN [Category]='APTM' THEN [Category] ELSE NULL END) AS PinPad,
COUNT(CASE WHEN [Category]='Monitor' THEN [Category] ELSE NULL END) AS Monitor,
COUNT(CASE WHEN [Category]='Laptop' THEN [Category] ELSE NULL END) AS Laptop,
COUNT(CASE WHEN [Category]='Network' THEN [Category] ELSE NULL END) AS Network
FROM SCORE
)SCORE
ON Score.[Transit]= AMDB_Master.[BTR Transit list]
and AMDB_Master.Desktop=SCORE.Desktop
and AMDB_Master.Printer = SCORE.Printer
and AMDB_Master.PinPad = SCORE.PinPad
and AMDB_Master.Monitor = SCORE.Monitor
and AMDB_Master.Laptop = SCORE.Laptop
and AMDB_Master.Network = SCORE.Network

And this is the result.

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'AMDB_Master'.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 'SCORE'.

EEEK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 12:16:22

quote:
Originally posted by micrapip

k here is what I did for the Match

SELECT [BTR Transit list],
q1.Desktop,
q2.Desktop,
q1.Printer,
q2.Printer,
q1.PinPad,
q2.PinPad,
q1.Monitor,
q2.Monitor,
q1.Laptop,
q2.Laptop,
q1.Network,
q2.Network
FROM (SELECT [BTR Transit list],
COUNT(CASE WHEN [Category_class]='PRINTER' THEN [Category_class] ELSE NULL END) AS Printer,
COUNT(CASE WHEN [Category_class]='Desktop/Tower' THEN [Category_class] ELSE NULL END)AS Desktop,
COUNT(CASE WHEN [Category_class]='APTM' THEN [Category_class] ELSE NULL END) as PinPad,
COUNT(CASE WHEN [Category_class]='Monitor' THEN [Category_class] ELSE NULL END)as Monitor,
COUNT(CASE WHEN [Category_class]='Laptop' THEN [Category_class] ELSE NULL END)as Laptop,
COUNT(CASE WHEN [Category_class]='Network' THEN [Category_class] ELSE NULL END)as Network
FROM AMDB_Master
where [Flex9] IN( 'RFSI08-032' ,'RFS!08-032' , 'RFSI08023', 'RFSI08032','RFSI-0832' , 'RFSI-0832' , 'RFSI8-032' ,'N/A')
GROUP BY [BTR Transit list]
)q1
INNER JOIN (
SELECT [Transit],
COUNT(CASE WHEN [Category]='Printer' THEN [Category] ELSE NULL END) AS Printer,
COUNT(CASE WHEN [Category]='Desktop' THEN [Category] ELSE NULL END) AS Desktop,
COUNT(CASE WHEN [Category]='APTM' THEN [Category] ELSE NULL END) AS PinPad,
COUNT(CASE WHEN [Category]='Monitor' THEN [Category] ELSE NULL END) AS Monitor,
COUNT(CASE WHEN [Category]='Laptop' THEN [Category] ELSE NULL END) AS Laptop,
COUNT(CASE WHEN [Category]='Network' THEN [Category] ELSE NULL END) AS Network
FROM SCORE
GROUP BY [Transit]
)q2
ON q2.[Transit]= q1.[BTR Transit list]
and q1.Desktop=q2.Desktop
and q1.Printer = q2.Printer
and q1.PinPad = q2.PinPad
and q1.Monitor = q2.Monitor
and q1.Laptop = q2.Laptop
and q1.Network = q2.Network

And this is the result.

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'AMDB_Master'.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 'SCORE'.

EEEK

try like above

micrapip
Starting Member

11 Posts

Posted - 2008-12-07 : 08:28:47

Thanks, sooo much, this worked perfectly and I was able to get it to work with the <>.

I now need something further for my analysis. When the count is <> I need to display the asset tag(s) of the higher amount...

If Transit 123 has a count of desktops of 10 in AMDB_Master table and 15 in Score table I need to display the list of 5 asset tags from Score tbl and vice versa if the count is higher in the AMDB_Master tbl

In AMDB_Master its Asset_Tag
In SCORE its SBC_Tag

I was going to post what I attempted but it isn't even close.

If you can help me that would be great!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 11:39:16

quote:
Originally posted by micrapip

Thanks, sooo much, this worked perfectly and I was able to get it to work with the <>.

I now need something further for my analysis. When the count is <> I need to display the asset tag(s) of the higher amount...

If Transit 123 has a count of desktops of 10 in AMDB_Master table and 15 in Score table I need to display the list of 5 asset tags from Score tbl and vice versa if the count is higher in the AMDB_Master tbl

In AMDB_Master its Asset_Tag
In SCORE its SBC_Tag

I was going to post what I attempted but it isn't even close.

If you can help me that would be great!!!

do you mean selecting the difference in number (15-10=5) or do you want to list out details of 5 rows?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 12:30:02

Thinking about this further, i have a doubt. suppose you've 10 desktops in AMDB and 15 in Score, will be you be always certain that Score will contain all 10 of AMDB + 5 extra. Can there be a chance that Score contain only 8 of AMDB and 7 new whereas AMDB has the 8 in score + 2 new. If such cases occur, do you want to return only Score's or both tables tags which dont exist in other?

micrapip
Starting Member

11 Posts

Posted - 2008-12-07 : 21:45:40

Let me explain further...

When I ran the Query where there were differences between types, let's say desktops, some times AMDB_Master had more than SCORE and sometimes SCORE had more.

So lets say AMDB_Master had 10 desktop's and Score had 8.

What I need is the 2 records that are in AMDB and not in SCORE I will need to sort on the Asset tags in case there are differences in the Asset Tag numbers...

But I need the asset tags that are missing so I can assign these to staff to investigate...

I will give you a little info... I work for a bank, and in 8 months we replaced over 60,000 devices in 1200 branches... i.e. desktops, laptops, pinpads... etc. and it's my job to reconcile the asset management database to the shipping database. So not a small feat..

Hope this helps

If not I will add more

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 03:33:08

so regardless of whether you have any tags in low count table which is not in other, you just want details of ones in higher count table alone?

micrapip
Starting Member

11 Posts

Posted - 2008-12-08 : 07:36:04

That is correct

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources