Troublesome select statement... Help, please? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Troublesome select statement... Help, please?

Author

Topic

Greffin
Starting Member

10 Posts

Posted - 2008-10-20 : 05:55:29

Hi.

I have three tables in a query. In this forum, I will call them Master, Log and Template. The table relationships are as follows: Master is joined to Log in a one-to-many relation using a Master_ID field. Log is joined to Template in a many-to-one relation using Template_ID field.

For each new Master_ID, I wish to display all registered fields in Log. If there are any records in Template which is not referred to in the Master-to-Log query, I wish to display these also.

This is the query I've come up with so far:


SELECT Master.Master_ID,
       Log.Template_ID,
       Template.ShortName
FROM   Master, Log, Template
WHERE  Master.Master_ID = Log.Master_ID
AND    Log.Template_ID = Template.Template_ID
UNION
SELECT null,
       Template.Template_ID,
       Template.ShortName
FROM   Template

But this select will give me double up of all records which is registered in both Template and Log. My goal here is to display all Log records, and only Template records missing from Log.

Can anybody help me here? Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 06:00:23

[code]SELECT Master.Master_ID,
Log.Template_ID,
Template.ShortName
FROM Master
JOIN Log
ON Master.Master_ID = Log.Master_ID
JOIN Template
ON Log.Template_ID = Template.Template_ID
UNION ALL
SELECT null,
Template.Template_ID,
Template.ShortName
FROM Template t
LEFT JOIN Log l
ON l.Template_ID = t.Template_ID
WHERE l.Template_ID IS NULL[/code]

Greffin
Starting Member

10 Posts

Posted - 2008-10-20 : 07:21:30

Thank you for your quick response, but unfortunately this did not work as expected.
Let me be more precise. From the example above,

 
Master = DeployedVersion. 
Log    = Log. 
Tasks  = Template.

This is the SQL I'm trying to perform:


SELECT 
  DeployedVersion.Site_ID, 
  DeployedVersion.Version_ID, 
  Tasks.Task_ID,
  Tasks.ShortName, 
  Tasks.Sequence, 
  Log.CreatedDateTime, 
  Log.Comment
FROM Tasks 
INNER JOIN (DeployedVersion INNER JOIN Log ON DeployedVersion.Deployment_ID = Log.Deployment_ID) ON Tasks.Task_ID = Log.Task_ID
UNION ALL
SELECT 
  null,
  null,
  Tasks.Task_ID,
  Tasks.ShortName,
  Tasks.Sequence,
  null,
  null
FROM
  Tasks
  LEFT JOIN Log ON Log.Task_ID = Tasks.Task_ID
WHERE Log.Task_ID is null

This gives me two rows of data:


Site_ID	Version_ID	Task_ID	ShortName	Sequence	CreatedDateTime	Comment
ACL		2		1		Task1		1		20.10.2008	
ACL		2		2		Task2		2		20.10.2008

Whereas I would have expected the following result:


Site_ID	Version_ID	Task_ID	ShortName	Sequence	CreatedDateTime	Comment
ACL		2		1		Task1		1		20.10.2008	
ACL		2		2		Task2		2		20.10.2008	
				3		Task3		3			
				4		Task4		4			
				5		Task5		5			
 ... and so on depending on the number of Tasks available.

How can I achieve this?

Greffin
Starting Member

10 Posts

Posted - 2008-10-20 : 07:27:29

quote:

Whereas I would have expected the following result:

Hmm.. Let me correct myself. THIS is the preferred result:


Site_ID	Version_ID	Task_ID	ShortName	Sequence	CreatedDateTime	Comment
ACL		2		1		Task1		1		20.10.2008	
ACL		2		2		Task2		2		20.10.2008	
ACL		2		3		Task3		3			
ACL		2		4		Task4		4			
ACL		2		5		Task5		5

This will give me the master record in the first two columns, the template fields in the next 3, and the Log entries (if any) in the last two columns.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 07:42:49

how will be data existing in your tables for above output?

Greffin
Starting Member

10 Posts

Posted - 2008-10-20 : 07:59:41

quote:
Originally posted by visakh16

how will be data existing in your tables for above output?

DeployedVersion


Deployment_ID	Version_ID	Site_ID	DeployedDateTime
4			2.9.0.0	ACL		20.10.2008 12:00:00
5			2.9.0.0	AAZ		20.10.2008 10:00:00
6			2.9.0.0	ADE		21.10.2008 10:00:00

Log


ID	Deployment_ID	Task_ID	CreatedDateTime		Comment
30	4			1		20.10.2008 11:36:25	
31	4			2		20.10.2008 11:49:03	
32	5			1		20.10.2008 09:12:23	
33	5			2		20.10.2008 09:16:43	
34	5			3		20.10.2008 09:47:20
35	5			4		20.10.2008 10:00:00	
36	5			5		20.10.2008 10:01:17	
37	5			6		20.10.2008 10:01:43

Tasks


Task_ID	ShortName	Sequence
1		Task1		1
2		Task2		2
3		Task3		3
4		Task4		4
5		Task5		1
6		Task6		2

A select using Deployment_ID 5 as filter, would give me all rows registered in Log, but a select for Deployment_ID 4 would give me the two rows registered in Log and the rest from Tasks. A select using Deployment_ID 6 would not give me any rows from Log, but fetch all rows from Tasks.

I hope I'm making myself clear in my purpose here.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 08:09:12

[code]SELECT Deployment_ID,Task_ID,Createddatetime
FROM DeployedVersion dv
INNER JOIN Log l
ON l.Deployment_ID=dv.Deployment_ID
INNER JOIN Tasks t
ON t.Task_ID=l.Task_ID
WHERE dv.Deployment_ID=@Deployment_ID
UNION ALL
SELECT NULL,Task_ID,NULL
FROM Tasks t
INNER JOIN Log l
ON t.Task_ID=l.Task_ID
AND l.Deployment_ID=@Deployment_ID
WHERE l.Task_ID IS NULL[/code]

Greffin
Starting Member

10 Posts

Posted - 2008-10-20 : 11:46:59

Again, thank you, but this is still not correct.

This is now my sql:


SELECT 
  dv.Site_ID, 
  dv.Version_ID, 
  t.Task_ID,
  t.ShortName, 
  t.Sequence, 
  l.CreatedDateTime, 
  l.Comment
FROM DeployedVersion dv
INNER JOIN (Log l INNER JOIN Tasks t ON t.Task_ID=l.Task_ID) ON l.Deployment_ID=dv.Deployment_ID 
WHERE dv.Deployment_ID=@Deployment_ID
UNION ALL
SELECT 
  null,
  null,
  t.Task_ID,
  t.ShortName,
  t.Sequence,
  null,
  null
FROM  Tasks t
INNER JOIN Log l ON t.Task_ID=l.Task_ID
WHERE l.Task_ID IS NULL
AND l.Deployment_ID=@Deployment_ID

which when run and filtered on Deployment_ID 4, gives me 2 rows. When filtered on Deployment_ID 5, gives me 6 rows. On Deployment_ID 6, gives me 0 rows.

Is there any way I can get it to allways return 6 rows (the number of registered Tasks)?

Perhaps I should inform you guys that I am using Access for this. It's a mini-mini application, so I do not require a heavy SQL DB.

I still appreciate every response.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 12:00:04

the query you've used is different from what i posted. try it like below and see


SELECT 
  dv.Site_ID, 
  dv.Version_ID, 
  t.Task_ID,
  t.ShortName, 
  t.Sequence, 
  l.CreatedDateTime, 
  l.Comment
FROM DeployedVersion dv
INNER JOIN (Log l INNER JOIN Tasks t ON t.Task_ID=l.Task_ID) ON l.Deployment_ID=dv.Deployment_ID 
WHERE dv.Deployment_ID=@Deployment_ID
UNION ALL
SELECT 
  null,
  null,
  t.Task_ID,
  t.ShortName,
  t.Sequence,
  null,
  null
FROM  Tasks t
LEFT JOIN Log l ON t.Task_ID=l.Task_ID
AND l.Deployment_ID=@Deployment_ID
WHERE l.Task_ID IS NULL

Greffin
Starting Member

10 Posts

Posted - 2008-10-20 : 12:15:54

quote:
Originally posted by visakh16

the query you've used is different from what i posted. try it like below and see

Access reports: JOIN expression not supported. That's why I tried to rewrite it a bit.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 12:39:40

quote:
Originally posted by Greffin

quote:
Originally posted by visakh16

the query you've used is different from what i posted. try it like below and see

Access reports: JOIN expression not supported. That's why I tried to rewrite it a bit.

then post in Access forums to get more help on this.

Greffin
Starting Member

10 Posts

Posted - 2008-10-21 : 02:48:57

Got it working finally. Needed to put in extra where clauses for this:


SELECT
  dv.Site_ID,
  dv.Version_ID,
  t.Task_ID,
  t.ShortName,
  t.Sequence,
  l.CreatedDateTime,
  l.Comment
FROM DeployedVersion dv
INNER JOIN (Log l INNER JOIN Tasks t ON t.Task_ID=l.Task_ID) ON l.Deployment_ID=dv.Deployment_ID
WHERE dv.Deployment_ID=@Deployment_ID
UNION ALL
SELECT distinct
  (SELECT dv.Site_ID FROM DeployedVersion dv WHERE dv.Deployment_ID = @Deployment_ID),
  (SELECT dv.Version_ID FROM DeployedVersion dv WHERE dv.Deployment_ID = @Deployment_ID),
  t.Task_ID,
  t.ShortName,
  t.Sequence,
  null,
  null
FROM  Tasks t
LEFT JOIN Log l ON t.Task_ID=l.Task_ID
WHERE l.Task_ID IS null OR l.Task_ID not in (SELECT l.Task_ID FROM Log l WHERE l.Deployment_ID = @Deployment_ID)
ORDER BY t.Task_ID;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 02:56:57

isnt this?

SELECT distinct
(SELECT dv.Site_ID FROM DeployedVersion dv WHERE dv.Deployment_ID = @Deployment_ID),
(SELECT dv.Version_ID FROM DeployedVersion dv WHERE dv.Deployment_ID = @Deployment_ID),
t.Task_ID,
t.ShortName,
t.Sequence,
null,
null
FROM Tasks t
LEFT JOIN Log l ON t.Task_ID=l.Task_ID
WHERE l.Task_ID IS null OR l.Task_ID not in (SELECT l.Task_ID FROM Log l WHERE l.Deployment_ID = @Deployment_ID)
ORDER BY t.Task_ID;
same as

SELECT distinct
  (SELECT dv.Site_ID FROM DeployedVersion dv WHERE dv.Deployment_ID = @Deployment_ID),
  (SELECT dv.Version_ID FROM DeployedVersion dv WHERE dv.Deployment_ID = @Deployment_ID),
  t.Task_ID,
  t.ShortName,
  t.Sequence,
  null,
  null
FROM  Tasks t
LEFT JOIN Log l ON t.Task_ID=l.Task_ID
AND l.Deployment_ID = @Deployment_ID
WHERE l.Task_ID IS null 
ORDER BY t.Task_ID;

which is what i gave earlier

Greffin
Starting Member

10 Posts

Posted - 2008-10-21 : 03:59:46

quote:
Originally posted by visakh16
isnt this same as what i gave earlier

Nope. These lines differ, and make all the difference:


LEFT JOIN Log l ON t.Task_ID=l.Task_ID
WHERE l.Task_ID IS null OR l.Task_ID not in (SELECT l.Task_ID FROM Log l WHERE l.Deployment_ID = @Deployment_ID)

...

LEFT JOIN Log l ON t.Task_ID=l.Task_ID
AND l.Deployment_ID = @Deployment_ID
WHERE l.Task_ID IS null

Apparently you cannot state AND in a FROM clause.

But from one thing to another. I would like to remove the @Deployment_ID filter. If I have 3 rows in DeployedVersion and 7 rows in Tasks, the query should return 3x7 = 21 rows regardless of what is stored in Log. How can this be achieved?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 04:20:08

quote:
Originally posted by Greffin

quote:
Originally posted by visakh16
isnt this same as what i gave earlier

Nope. These lines differ, and make all the difference:

LEFT JOIN Log l ON t.Task_ID=l.Task_ID
WHERE l.Task_ID IS null OR l.Task_ID not in (SELECT l.Task_ID FROM Log l WHERE l.Deployment_ID = @Deployment_ID)

...

LEFT JOIN Log l ON t.Task_ID=l.Task_ID
AND l.Deployment_ID = @Deployment_ID
WHERE l.Task_ID IS null

Apparently you cannot state AND in a FROM clause.

But from one thing to another. I would like to remove the @Deployment_ID filter. If I have 3 rows in DeployedVersion and 7 rows in Tasks, the query should return 3x7 = 21 rows regardless of what is stored in Log. How can this be achieved?

nope you missed my point. what i was asking was wont the two queries (one i gave and yours) give same result? Also i havent used AND in FROM . Didnt understand what you specified above.

Greffin
Starting Member

10 Posts

Posted - 2008-10-21 : 05:19:50

In fear of seaming like a newbie (which I actually am in Access and MS SQL context), I will answer as best I can.

quote:
Originally posted by visakh16
nope you missed my point. what i was asking was wont the two queries (one i gave and yours) give same result?

Perhaps. The trouble was that the one you posted could not be run in Access.

quote:
Originally posted by visakh16
Also i havent used AND in FROM . Didnt understand what you specified above.

The query you posted was built like

SELECT .. FROM .. LEFT JOIN ON .. AND ... WHERE ...

where Access complained about the AND part. Access also complains when you don't nest joins. You cannot have

SELECT ... FROM ... INNER JOIN ON ... INNER JOIN

. You must nest them like

SELECT ... FROM ... INNER JOIN (Table INNER JOIN ON) ON ...

I don't know why, but perhaps the SQL engine of Access isn't as forgiving as the one in MS SQL. I was originally under the impression that these two engines were mostly the same.

Anyway, thanks for wanting to help.

Do you have any suggestions on how to rewrite the SQL in order to fit the requirements in my last post? If I have 3 rows in DeployedVersion and 7 rows in Tasks, the query should return 3x7 = 21 rows regardless of what is stored in Log. How can this be achieved?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 05:29:05

quote:
Originally posted by Greffin

Do you have any suggestions on how to rewrite the SQL in order to fit the requirements in my last post? If I have 3 rows in DeployedVersion and 7 rows in Tasks, the query should return 3x7 = 21 rows regardless of what is stored in Log. How can this be achieved?

this can be achieved by using CROSS JOIN. just use

select fields from table1 CROSS JOIN table2

as before i'm not sure whether this works in Access or not. In MSSQL it gives you 7*3 rows. (Thats why i told to post in Access forums to get access specific solution)

Greffin
Starting Member

10 Posts

Posted - 2008-10-21 : 06:14:30

quote:
Originally posted by visakh16
this can be achieved by using CROSS JOIN. just use
select fields from table1 CROSS JOIN table2

as before i'm not sure whether this works in Access or not. In MSSQL it gives you 7*3 rows. (Thats why i told to post in Access forums to get access specific solution)

OMG.. That was such an easy solution. Googling Cross joins and Access gave me what I needed. Thank you soooo much.

This is my SQL:


SELECT 
  dv.Deployment_ID,
  dv.Site_ID, 
  dv.Version_ID, 
  t.Task_ID,
  t.ShortName, 
  t.Sequence, 
  (SELECT l.CreatedDateTime from Log l where l.Task_ID = t.Task_ID AND l.Deployment_ID = dv.Deployment_ID),
  (SELECT l.Comment from Log l where l.Task_ID = t.Task_ID AND l.Deployment_ID = dv.Deployment_ID)
FROM DeployedVersion dv, 
  Tasks t
ORDER BY dv.Site_ID, dv.Version_ID, t.Task_ID

Again thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 06:34:06

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources