Joins - SQL Server Forums

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)

Joins

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-28 : 09:37:56

What do you do

when you have to use lots of tables in order to get the columns you need, but each time you join it there are so many extra rows you don't want in there?

Maybe it comes down to grouping to get rid of all these rows?

After joining 6 big tables to get these columns together, I'm looking for about 5 - 10 rows each time, each one a different set of details to send as an email to each recipient using xp_smtp_sendmail in HTML. The join gives me 30,000 rows. I'm trying mixtures of inner and left outer joins and did get this down to 515 rows, but no idea how.

The one thing I can't understand is I've 'flagged' a status in one column in a table. Regardless of any joins, I should be able to specify WHERE status = 'PENDING' and where that gives 10 results, that's what I want to see. Even if there are 30,000 rows, that WHERE should cut it down to that, shouldn't it? It finds these 10 in the one table. With the joins, all the rows turn up. What's happening?

Any tips on joins?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 09:41:23

Most often I use derived tables.

SELECT *
FROM ( select col, col2 from table where col4 = 't' and col2 >= getdate()
) AS d
inner join ( ... ) as f on ... = ....

to keep each set as small as possible.

E 12°55'05.25"
N 56°04'39.16"

Imukai
Starting Member

29 Posts

Posted - 2008-03-28 : 09:44:29

Really depends how the joins are set up as to what you're going to get, even with a WHERE clause. Odds are, all of your rows do have that status, but there's a bunch of data repeating because of your joins.

We'd probably need to see the query you've created, and maybe a small sample (10-20 rows maybe) of the resultant set.

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-28 : 10:47:22

drop table #validSOR
select distinct
identity(int) as x,
ccs_qos_item_ref "SOR",
ccs_mee_oper_name "Operative",
ccs_cd_email "email",
ccs_quo_user_date#1 "upd_date",
received "received",
ccs_not_text#1 "text1",
ccs_not_text#2 "text2",
ccs_not_text#3 "text3",
ccs_quo_ref_number#2 "jobno",
ccs_quo_site_contact "Customer",
ccs_quo_company_ref "Client",
ccs_quo_address#1 "Address1",
ccs_quo_address#2 "Address2",
ccs_quo_address#3 "Address3",
ccs_quo_address#4 "Address4",
ccs_quo_address#5 "Address5",
ccs_quo_address#6 "Address6",
ccs_qos_title "Descrip",
ccs_qos_qty#2 "Quantity",
ccs_quo_analysis_code "Status",
ccs_qos_quoted_price#6 "Cost"
into #validSOR
from [ken-sql-002].constructmobile.dbo.ccs_quotes q
left outer join [ken-sql-002].hettonhosttestdatabase.dbo.job j
on q.ccs_quo_ref_number#2 = left(j.cid,10)
inner join [ken-sql-002].constructmobile.dbo.ccs_q_sections qs
on q.ccs_quo_quote_number = qs.ccs_qos_quote_number
inner join [ken-sql-002].constructmobile.dbo.ccs_site_meeting m
on m.ccs_mee_quote_number = q.ccs_quo_quote_number
inner join [ken-sql-002].constructmobile.dbo.ccs_notes n
on q.ccs_quo_note_number = n.ccs_not_number
inner join [ken-sql-002].constructmobile.dbo.ccs_company_file f -- email
on q.ccs_quo_company_ref = f.ccs_cd_account_number
--where ccs_qos_item_ref is not NULL
--where ccs_quo_ref_number#2 = 'THAD003736'
where q.ccs_quo_analysis_code = 'PENDING'
--where ccs_qos_item_ref in ('631501','372019','631107','O/H/R','371031','0Y0000','DECS') -- these are working SOR samples

select * from #validSOR

inserting into to be able to use the identity(int) as x

resultset crop: 515

1 ADADAD6 PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Reposition WHB 1.0000 PENDING 50.54
2 ADADAD10 PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Supply/fit level access shower tray 1.0000 PENDING 887.11
3 BLANK PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Unknown Library Item Ref 1.0000 PENDING .00
4 892003 PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Unknown Library Item Ref 10.0000 PENDING .00
5 898121 PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Unknown Library Item Ref 5.0000 PENDING .00
6 ADADAD6 PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Reposition WHB 1.0000 PENDING 50.54
7 ADADAD10 PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Supply/fit level access shower tray 1.0000 PENDING 887.11
8 BLANK PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Unknown Library Item Ref 1.0000 PENDING .00
9 892003 PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Unknown Library Item Ref 10.0000 PENDING .00
10 898121 PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Unknown Library Item Ref 5.0000 PENDING .00
11 ADADAD6 PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Reposition WHB 1.0000 PENDING 50.54
12 ADADAD10 PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Supply/fit level access shower tray 1.0000 PENDING 887.11
13 BLANK PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Unknown Library Item Ref 1.0000 PENDING .00
14 892003 PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Unknown Library Item Ref 10.0000 PENDING .00
15 898121 PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Unknown Library Item Ref 5.0000 PENDING .00
16 ADADAD6 PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Reposition WHB 1.0000 PENDING 50.54
17 ADADAD10 PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Supply/fit level access shower tray 1.0000 PENDING 887.11
18 BLANK PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Unknown Library Item Ref 1.0000 PENDING .00
19 892003 PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Unknown Library Item Ref 10.0000 PENDING .00
20 898121 PAUL CHESNEY 2008-03-28 08:55:51.000 NULL TEST000001 MR & MRS UNREAL RSOUT02 123 MAKEBELIEVE STREET 123 MAKEBELIEVE STREET SE11 5RH Unknown Library Item Ref 5.0000 PENDING .00

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 10:57:43

You have two performance killers here

drop table	#validSOR

select distinct	identity(int) as x, 
		ccs_qos_item_ref as [SOR], 
		ccs_mee_oper_name as [Operative], 
		ccs_cd_email as [email],
		ccs_quo_user_date#1 as [upd_date], 
		received,
		[ccs_not_text#1] AS text1,
		[ccs_not_text#2] as [text2],
		[ccs_not_text#3] as [text3],
		[ccs_quo_ref_number#2] as jobno,
		ccs_quo_site_contact as Customer,
		ccs_quo_company_ref as Client,
		[ccs_quo_address#1] AS Address1,
		[ccs_quo_address#2] as Address2,
		[ccs_quo_address#3] as Address3,
		[ccs_quo_address#4] as Address4,
		[ccs_quo_address#5] as Address5,
		[ccs_quo_address#6] as Address6,
		ccs_qos_title as Descrip,
		[ccs_qos_qty#2] as Quantity,
		ccs_quo_analysis_code AS Status,
		[ccs_qos_quoted_price#6] as Cost
into		#validSOR
from		[ken-sql-002].constructmobile.dbo.ccs_quotes AS q 
left join	[ken-sql-002].hettonhosttestdatabase.dbo.job AS j on q.[ccs_quo_ref_number#2] = left(j.cid, 10)
inner join	[ken-sql-002].constructmobile.dbo.ccs_q_sections as qs on q.ccs_quo_quote_number = qs.ccs_qos_quote_number
inner join	[ken-sql-002].constructmobile.dbo.ccs_site_meeting as m on m.ccs_mee_quote_number = q.ccs_quo_quote_number
inner join	[ken-sql-002].constructmobile.dbo.ccs_notes as n on q.ccs_quo_note_number = n.ccs_not_number
inner join	[ken-sql-002].constructmobile.dbo.ccs_company_file as f on q.ccs_quo_company_ref = f.ccs_cd_account_number
where		q.ccs_quo_analysis_code = 'PENDING'

select * from #validSOR

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 10:59:33

1) Drop the DISTINCT. Every record will be unique anyway due to the use of IDENTITY function.
2) The JOIN binding clause.

E 12°55'05.25"
N 56°04'39.16"

Imukai
Starting Member

29 Posts

Posted - 2008-03-28 : 11:13:06

Ok.. your issue is coming in from your left join of:

[ken-sql-002].hettonhosttestdatabase.dbo.job j ON q.ccs_quo_ref_number#2 = left(j.cid,10)

Your hettonhosttestdatabase.dbo.job table must contain a bunch of records with the same "cid" field (TEST000001). By doing a left join in this fashion, you're pulling, basically, every single row from hettonhosttestdatabase.dbo.job table with the left 10 characters of the "cid" field matching ccs_quo_ref_number#2 field of [ken-sql-002].constructmobile.dbo.ccs_quotes (or "q" in your insert).

There has to be some other field within hettonhosttestdatabase.dbo.job table that could narrow down what data you are wanting from that particular table. You could add that 2nd field to the WHERE clause (which I believe basically turns your LEFT OUTER into an INNER join at that point), or you could add it to the LEFT OUTER JOIN clause itself:

i.e.

LEFT OUTER JOIN [ken-sql-002].hettonhosttestdatabase.dbo.job j ON q.ccs_quo_ref_number#2 = left(j.cid,10) AND j.somefield = 'somevalue'

The key that you have to figure out is, "what do I want from hettonhosttestdatabase.dbo.job table in relation to the rest of this query" and structure it accordingly.

If this doesn't help you figure it out, what is contained in the hettonhosttestdatabase.dbo.job table? And which of your SELECT fields is being derived from that table?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 11:20:10

Fill in all missing table alias prefixes and we might be able to assist you further

DROP TABLE	#ValidSOR

SELECT		IDENTITY(INT, 1, 1) AS x,
		{q | j | qs | m | n | f }.ccs_qos_item_ref AS SOR,
		{q | j | qs | m | n | f }.ccs_mee_oper_name AS Operative,
		{q | j | qs | m | n | f }.ccs_cd_email AS eMail,
		{q | j | qs | m | n | f }.ccs_quo_user_date#1 AS upd_Date,
		{q | j | qs | m | n | f }.received,
		{q | j | qs | m | n | f }.[ccs_not_text#1] AS text1,
		{q | j | qs | m | n | f }.[ccs_not_text#2] AS text2,
		{q | j | qs | m | n | f }.[ccs_not_text#3] AS text3,
		{q | j | qs | m | n | f }.[ccs_quo_ref_number#2] AS jobno,
		{q | j | qs | m | n | f }.ccs_quo_site_contact AS Customer,
		{q | j | qs | m | n | f }.ccs_quo_company_ref AS Client,
		{q | j | qs | m | n | f }.[ccs_quo_address#1] AS Address1,
		{q | j | qs | m | n | f }.[ccs_quo_address#2] AS Address2,
		{q | j | qs | m | n | f }.[ccs_quo_address#3] AS Address3,
		{q | j | qs | m | n | f }.[ccs_quo_address#4] AS Address4,
		{q | j | qs | m | n | f }.[ccs_quo_address#5] AS Address5,
		{q | j | qs | m | n | f }.[ccs_quo_address#6] AS Address6,
		{q | j | qs | m | n | f }.ccs_qos_title AS Descrip,
		{q | j | qs | m | n | f }.[ccs_qos_qty#2] AS Quantity,
		{q | j | qs | m | n | f }.ccs_quo_analysis_code AS [Status],
		{q | j | qs | m | n | f }.[ccs_qos_quoted_price#6] AS Cost
INTO		#ValidSOR
FROM		[ken-sql-002].constructmobile.dbo.ccs_quotes AS q
LEFT JOIN	[ken-sql-002].hettonhosttestdatabase.dbo.job AS j ON LEFT(j.cid, 10) = q.[ccs_quo_ref_number#2]
INNER JOIN	[ken-sql-002].constructmobile.dbo.ccs_q_sections AS qs ON qs.ccs_qos_quote_number = q.ccs_quo_quote_number
INNER JOIN	[ken-sql-002].constructmobile.dbo.ccs_site_meeting AS m ON m.ccs_mee_quote_number = q.ccs_quo_quote_number
INNER JOIN	[ken-sql-002].constructmobile.dbo.ccs_notes AS n ON n.ccs_not_number = q.ccs_quo_note_number
INNER JOIN	[ken-sql-002].constructmobile.dbo.ccs_company_file AS f ON f.ccs_cd_account_number = q.ccs_quo_company_ref
WHERE		q.ccs_quo_analysis_code = 'PENDING'

SELECT	*
FROM	#ValidSOR

E 12°55'05.25"
N 56°04'39.16"

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-28 : 11:29:54

Thanks Imukai.

select top 5 ccs_quo_ref_number#2 from [ken-sql-002].constructmobile.dbo.ccs_quotes q

gives me a 10-char column from q which I need to join with j. The difference is stripping away anything in j.cid after 10 chars. So the left function merely does that. Does the left fiddle with the join syntax?

select top 5 cid from [ken-sql-002].hettonhosttestdatabase.dbo.job j

gives me this: GHAU269455/372951 after the slash is unwanted, stored in j

select top 5 left(j.cid,10) from [ken-sql-002].hettonhosttestdatabase.dbo.job j

doing this makes the join right, I think. Please tell me if I've caused trouble. I need to match those first 10 chars with q and j

Imukai
Starting Member

29 Posts

Posted - 2008-03-28 : 11:52:16

The matching isnt the problem - it's matching those just fine. The problem is since there's nothing else specified for that second table ([ken-sql-002].hettonhosttestdatabase.dbo.job), it's bringing in everything that matches that join.

Check to see how many rows you have in [ken-sql-002].hettonhosttestdatabase.dbo.job that have a "cid" field starting with TEST000001 - that's how many rows you're getting returned for that first guy (PAUL CHESNEY).

Peso is asking kinda what I wanted to know too.. can you tell us which tables those fields in the SELECT are coming from? I'm particularly interested in which field(s) you want to draw from [ken-sql-002].hettonhosttestdatabase.dbo.job (j).

Also, what other data is stored in your [ken-sql-002].hettonhosttestdatabase.dbo.job table? We have to identify a secondary characteristic in that data to make it whittle down to just the data you want.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 12:07:11

No, not really.
You can try replacing

LEFT JOIN	[ken-sql-002].hettonhosttestdatabase.dbo.job AS j ON LEFT(j.cid, 10) = q.[ccs_quo_ref_number#2]

with

LEFT JOIN	(
			SELECT DISTINCT	LEFT(j.cid, 10) AS newCID,
					{Other columns used in outer query}
			FROM		[ken-sql-002].hettonhosttestdatabase.dbo.job
		) AS j ON j.newCID = q.[ccs_quo_ref_number#2]

That is why it is so important you prefix all columns with table aliases!

E 12°55'05.25"
N 56°04'39.16"

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-28 : 12:07:12

I had all these specified as comments to the right, but it looked bad in this HTML, so got rid of it.... here we go...

DROP TABLE #ValidSOR

SELECT IDENTITY(INT, 1, 1) AS x,
qs.ccs_qos_item_ref AS SOR,
m.ccs_mee_oper_name AS Operative,
f.ccs_cd_email AS eMail,
q.ccs_quo_user_date#1 AS upd_Date,
j.received,
n.[ccs_not_text#1] AS text1,
n.[ccs_not_text#2] AS text2,
n.[ccs_not_text#3] AS text3,
q.[ccs_quo_ref_number#2] AS jobno,
q.ccs_quo_site_contact AS Customer,
q.ccs_quo_company_ref AS Client,
q.[ccs_quo_address#1] AS Address1,
q.[ccs_quo_address#2] AS Address2,
q.[ccs_quo_address#3] AS Address3,
q.[ccs_quo_address#4] AS Address4,
q.[ccs_quo_address#5] AS Address5,
q.[ccs_quo_address#6] AS Address6,
qs.ccs_qos_title AS Descrip,
qs.[ccs_qos_qty#2] AS Quantity,
q.ccs_quo_analysis_code AS [Status],
qs.[ccs_qos_quoted_price#6] AS Cost
INTO #ValidSOR
FROM [ken-sql-002].constructmobile.dbo.ccs_quotes AS q
LEFT JOIN [ken-sql-002].hettonhosttestdatabase.dbo.job AS j ON LEFT(j.cid, 10) = q.[ccs_quo_ref_number#2]
INNER JOIN [ken-sql-002].constructmobile.dbo.ccs_q_sections AS qs ON qs.ccs_qos_quote_number = q.ccs_quo_quote_number
INNER JOIN [ken-sql-002].constructmobile.dbo.ccs_site_meeting AS m ON m.ccs_mee_quote_number = q.ccs_quo_quote_number
INNER JOIN [ken-sql-002].constructmobile.dbo.ccs_notes AS n ON n.ccs_not_number = q.ccs_quo_note_number
INNER JOIN [ken-sql-002].constructmobile.dbo.ccs_company_file AS f ON f.ccs_cd_account_number = q.ccs_quo_company_ref
WHERE q.ccs_quo_analysis_code = 'PENDING'

SELECT *
FROM #ValidSOR

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 12:10:08

That is why we use the tags [ c o d e ] and [ / c o d e ]
without spaces.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 12:12:34

What do you want to do with all matching records in j table?
If there are none, j.received will be NULL for sure.
But if there are two or more matching records? Then there will be duplicate rows in outer query
and j.received will have different values.

Which of the duplicate values for j.received do you want?
All? Latest/max?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-28 : 12:14:50

Maybe all you need is this rewrite?

LEFT JOIN	(
			SELECT		LEFT(j.cid, 10) AS newCID,
					MAX(received) AS received
			FROM		[ken-sql-002].hettonhosttestdatabase.dbo.job
			GROUP BY	LEFT(j.cid, 10)
		) AS j ON j.newCID = q.[ccs_quo_ref_number#2]

And full code here...

DROP TABLE	#ValidSOR

SELECT		IDENTITY(INT, 1, 1) AS x,
		qs.ccs_qos_item_ref AS SOR,
		m.ccs_mee_oper_name AS Operative,
		f.ccs_cd_email AS eMail,
		q.ccs_quo_user_date#1 AS upd_Date,
		j.received,
		n.[ccs_not_text#1] AS text1,
		n.[ccs_not_text#2] AS text2,
		n.[ccs_not_text#3] AS text3,
		q.[ccs_quo_ref_number#2] AS jobno,
		q.ccs_quo_site_contact AS Customer,
		q.ccs_quo_company_ref AS Client,
		q.[ccs_quo_address#1] AS Address1,
		q.[ccs_quo_address#2] AS Address2,
		q.[ccs_quo_address#3] AS Address3,
		q.[ccs_quo_address#4] AS Address4,
		q.[ccs_quo_address#5] AS Address5,
		q.[ccs_quo_address#6] AS Address6,
		qs.ccs_qos_title AS Descrip,
		qs.[ccs_qos_qty#2] AS Quantity,
		q.ccs_quo_analysis_code AS [Status],
		qs.[ccs_qos_quoted_price#6] AS Cost
INTO		#ValidSOR
FROM		[ken-sql-002].constructmobile.dbo.ccs_quotes AS q
LEFT JOIN	(
			SELECT		LEFT(cid, 10) AS newCID,
					MAX(received) AS received
			FROM		[ken-sql-002].hettonhosttestdatabase.dbo.job
			GROUP BY	LEFT(cid, 10)
		) AS j ON j.newCID = q.[ccs_quo_ref_number#2]
INNER JOIN	[ken-sql-002].constructmobile.dbo.ccs_q_sections AS qs ON qs.ccs_qos_quote_number = q.ccs_quo_quote_number
INNER JOIN	[ken-sql-002].constructmobile.dbo.ccs_site_meeting AS m ON m.ccs_mee_quote_number = q.ccs_quo_quote_number
INNER JOIN	[ken-sql-002].constructmobile.dbo.ccs_notes AS n ON n.ccs_not_number = q.ccs_quo_note_number
INNER JOIN	[ken-sql-002].constructmobile.dbo.ccs_company_file AS f ON f.ccs_cd_account_number = q.ccs_quo_company_ref
WHERE		q.ccs_quo_analysis_code = 'PENDING'

SELECT	*
FROM	#ValidSOR

E 12°55'05.25"
N 56°04'39.16"

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-28 : 12:40:04

Really like it! If it works, I'll have to get into HOW it works!

To start with, when I try running this, why do I get this error....?

Server: Msg 107, Level 16, State 2, Line 2
The column prefix 'j' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'j' does not match with a table name or alias name used in the query.

j is specified right there...

DROP TABLE #ValidSOR
SELECT IDENTITY(INT, 1, 1) AS x,
qs.ccs_qos_item_ref AS SOR,
m.ccs_mee_oper_name AS Operative,
f.ccs_cd_email AS eMail,
q.ccs_quo_user_date#1 AS upd_Date,
j.received,
n.[ccs_not_text#1] AS text1,
n.[ccs_not_text#2] AS text2,
n.[ccs_not_text#3] AS text3,
q.[ccs_quo_ref_number#2] AS jobno,
q.ccs_quo_site_contact AS Customer,
q.ccs_quo_company_ref AS Client,
q.[ccs_quo_address#1] AS Address1,
q.[ccs_quo_address#2] AS Address2,
q.[ccs_quo_address#3] AS Address3,
q.[ccs_quo_address#4] AS Address4,
q.[ccs_quo_address#5] AS Address5,
q.[ccs_quo_address#6] AS Address6,
qs.ccs_qos_title AS Descrip,
qs.[ccs_qos_qty#2] AS Quantity,
q.ccs_quo_analysis_code AS [Status],
qs.[ccs_qos_quoted_price#6] AS Cost
INTO #ValidSOR
FROM [ken-sql-002].constructmobile.dbo.ccs_quotes AS q
LEFT JOIN (
SELECT LEFT(j.cid, 10) AS newCID,
MAX(received) AS received
FROM [ken-sql-002].hettonhosttestdatabase.dbo.job
GROUP BY LEFT(j.cid, 10)
) AS j ON j.newCID = q.[ccs_quo_ref_number#2]
INNER JOIN [ken-sql-002].constructmobile.dbo.ccs_q_sections AS qs ON qs.ccs_qos_quote_number = q.ccs_quo_quote_number
INNER JOIN [ken-sql-002].constructmobile.dbo.ccs_site_meeting AS m ON m.ccs_mee_quote_number = q.ccs_quo_quote_number
INNER JOIN [ken-sql-002].constructmobile.dbo.ccs_notes AS n ON n.ccs_not_number = q.ccs_quo_note_number
INNER JOIN [ken-sql-002].constructmobile.dbo.ccs_company_file AS f ON f.ccs_cd_account_number = q.ccs_quo_company_ref
WHERE q.ccs_quo_analysis_code = 'PENDING'

SELECT *
FROM #ValidSOR

Imukai
Starting Member

29 Posts

Posted - 2008-03-28 : 12:44:26

I believe the issue is in that derived table. Change:

LEFT JOIN (
SELECT LEFT(j.cid, 10) AS newCID,
MAX(received) AS received
FROM [ken-sql-002].hettonhosttestdatabase.dbo.job
GROUP BY LEFT(j.cid, 10)
) AS j ON j.newCID = q.[ccs_quo_ref_number#2]

to

LEFT JOIN (
SELECT LEFT(cid, 10) AS newCID,
MAX(received) AS received
FROM [ken-sql-002].hettonhosttestdatabase.dbo.job
GROUP BY LEFT(cid, 10)
) AS j ON j.newCID = q.[ccs_quo_ref_number#2]

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-03-28 : 12:48:09

just needs j in there after the hetton table.... job

the nested query with group bit. What does it do?

In fact, it still comes back with 515 rows. Maybe I can tweak it further in there. Right now there happens to be 1 PENDING row I need coming back with the right detail, and the right number of rows when that varies. Definitely not 515

Thanks

Imukai
Starting Member

29 Posts

Posted - 2008-03-28 : 12:58:58

The nested bit is a "derived table" - its building a "table" on the fly based on that nested query, and joining that up to the rest of your outer query as if it were a normal data table.

As an experiment to make sure we're dealing with an overflow from that table, run this and see if you still get 515 rows.. basically taking all the other tables out of the equation and narrowing down the issue:

DROP TABLE #ValidSOR
SELECT IDENTITY(INT, 1, 1) AS x,
q.ccs_quo_user_date#1 AS upd_Date,
j.received,
q.[ccs_quo_ref_number#2] AS jobno,
q.ccs_quo_site_contact AS Customer,
q.ccs_quo_company_ref AS Client,
q.[ccs_quo_address#1] AS Address1,
q.[ccs_quo_address#2] AS Address2,
q.[ccs_quo_address#3] AS Address3,
q.[ccs_quo_address#4] AS Address4,
q.[ccs_quo_address#5] AS Address5,
q.[ccs_quo_address#6] AS Address6,
q.ccs_quo_analysis_code AS [Status]
INTO #ValidSOR
FROM [ken-sql-002].constructmobile.dbo.ccs_quotes AS q
LEFT JOIN (
SELECT LEFT(cid, 10) AS newCID,
MAX(received) AS received
FROM [ken-sql-002].hettonhosttestdatabase.dbo.job
GROUP BY LEFT(cid, 10)
) AS j ON j.newCID = q.[ccs_quo_ref_number#2]
WHERE q.ccs_quo_analysis_code = 'PENDING'

SELECT *
FROM #ValidSOR

If you do NOT get 515 here, then it might be in one of the other tables - slowly add their INNER JOINs back 1 at a time and re-run until you see 515 rows.

If you DO get 515 rows from what I've pasted above there, we'll go from there.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources