select		max(an.executiontime),
		max(blu.executiontime,
		max(cp.executiontime,
		max(gl.executiontime, 
		max(gw.executiontime,
		max(mor.executiontime,
		max(rlinv.executiontime,
		max(rlpr.executiontime,
		max(win.executiontime)
from		autonationcarinventory as an,
		bluenile_new as blu,
		carnivalpricing as cp,
		gaylordhotel as gl,
		greatwolf as gw,
		morganshotel as mor,
		royalinventory as rlinv,
		royalpricing as rlpr,
		winnebago as win

SELECT	(SELECT	MAX(executiontime) FROM autonationcarinventory),
	(SELECT MAX(executiontime) FROM bluenile_new),
	(SELECT MAX(executiontime) FROM carnivalpricing),
	(SELECT MAX(executiontime) FROM gaylordhotel),
	(SELECT MAX(executiontime) FROM greatwolf),
	(SELECT MAX(executiontime) FROM morganshotel),
	(SELECT MAX(executiontime) FROM royalinventory),
	(SELECT MAX(executiontime) FROM royalpricing),
	(SELECT MAX(executiontime) FROM winnebago)

Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

carn | carncount | carnraw | carncounteu
1    | 15        | 2       | 24

title   | count
carn    | 15
carnraw | 24

quote:

---

Originally posted by sqlchiq

trying to do something like this now

select (select executiontime carn, count(executiontime) carncount
from carnivalclean
group by executiontime),
(select executiontime carnraw, count(executiontime) carncounteu
from carnivalpricing
group by executiontime)


with a bunch of tables, but its giving me

Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I dont understand, isn't it basically the same thing as Peso's query? His works but my new one doesn't

---

select m.carn,m.carncount,tmp.carncounteu
from
(select executiontime as carn, count(executiontime) as carncount
from carnivalclean
group by executiontime)m
inner join (select executiontime as  carnraw, count(executiontime)  as carncounteu
from carnivalpricing
group by executiontime)tmp
on tmp.carnraw=m.carn

quote:

---

Originally posted by Peso

SELECT		t1.carn,
		t1.carncount,
		t2.carnraw,
		t2.carncounteu
FROM		(
			select		executiontime as carn,
					count(executiontime) AS carncount
			from		carnivalclean
			group by	executiontime
		) AS t1
FULL JOIN	(
			select		executiontime as carnraw,
					count(executiontime) as carncounteu
			from		carnivalpricing
			group by	executiontime
		) AS t2 ON t2.carnraw = t1.carn
ORDER BY	COALESCE(t1.carn, t2.carnraw) DESC

---

E 12°55'05.63"
N 56°04'39.26"

---

quote:

---

Originally posted by sqlchiq

quote:

---

Originally posted by Peso

SELECT		t1.carn,
		t1.carncount,
		t2.carnraw,
		t2.carncounteu
FROM		(
			select		executiontime as carn,
					count(executiontime) AS carncount
			from		carnivalclean
			group by	executiontime
		) AS t1
FULL JOIN	(
			select		executiontime as carnraw,
					count(executiontime) as carncounteu
			from		carnivalpricing
			group by	executiontime
		) AS t2 ON t2.carnraw = t1.carn
ORDER BY	COALESCE(t1.carn, t2.carnraw) DESC

---

E 12°55'05.63"
N 56°04'39.26"

---

Is there anyway to get ride of those nulls?

its going


null null 9/13 321
null null 9/12 323
null null 9/11 546
9/13 654 null null
9/12 465 null null
9/11 321 null null

right now


Also, I can add as many columns as I want right? I need to add about 5 more tables to this query

---

SELECT		t1.carn,
		t1.carncount,
		t2.carnraw,
		t2.carncounteu
FROM		(
			select		executiontime as carn,
					count(executiontime) AS carncount
			from		carnivalclean
			group by	executiontime
		) AS t1
FULL JOIN	(
			select		executiontime as carnraw,
					count(executiontime) as carncounteu
			from		carnivalpricing
			group by	executiontime
		) AS t2 ON t2.carnraw = t1.carn
ORDER BY	COALESCE(t1.carn, t2.carnraw) DESC

quote:

---

Originally posted by visakh16

you dont want nulls means you need inner join. then use the solution i provided. use inner join instead of full join.

---

DECLARE	@tableA TABLE
	(
		ID INT,
		s INT
	)

INSERT	@tableA
SELECT	1, 1000 UNION ALL
SELECT	2, 2000

DECLARE	@tableB TABLE
	(
		ID INT,
		t INT
	)

INSERT	@tableB
SELECT	2, 20 UNION ALL
SELECT	3, 30

-- Visakh16
SELECT		a.ID,
		a.s,
		b.ID,
		b.t
FROM		@tableA AS a
INNER JOIN	@tableB AS b ON b.ID = a.ID
ORDER BY	a.ID DESC

--Peso
SELECT		a.ID,
		a.s,
		b.ID,
		b.t
FROM		@tableA AS a
FULL JOIN	@tableB AS b ON b.ID = a.ID
ORDER BY	COALESCE(a.ID, b.ID) DESC

quote:

---

Originally posted by Peso

quote:

---

Originally posted by visakh16

you dont want nulls means you need inner join. then use the solution i provided. use inner join instead of full join.

---

Visakh and sqlchig, look at this example of how NULL in the joins are handled...

DECLARE	@tableA TABLE
	(
		ID INT,
		s INT
	)

INSERT	@tableA
SELECT	1, 1000 UNION ALL
SELECT	2, 2000

DECLARE	@tableB TABLE
	(
		ID INT,
		t INT
	)

INSERT	@tableB
SELECT	2, 20 UNION ALL
SELECT	3, 30

-- Visakh16
SELECT		a.ID,
		a.s,
		b.ID,
		b.t
FROM		@tableA AS a
INNER JOIN	@tableB AS b ON b.ID = a.ID
ORDER BY	a.ID DESC

--Peso
SELECT		a.ID,
		a.s,
		b.ID,
		b.t
FROM		@tableA AS a
FULL JOIN	@tableB AS b ON b.ID = a.ID
ORDER BY	COALESCE(a.ID, b.ID) DESC

See the difference?
Do you also see what happens when ID matches from both tables?

---

E 12°55'05.63"
N 56°04'39.26"

---