get value from subquery? - 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 2012 Forums

Transact-SQL (2012)

get value from subquery?

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-09-30 : 10:18:03

Hi

I have the following Query..


	SELECT PdfFileName, ID, IsFetched, DateAdded, [GUID], AppName
	FROM dbo.Cards
	WHERE (IsFetched IS NULL OR IsFetched = 0) AND (PdfFileName <> N'')
	ORDER BY DateAdded

This works almost as I want it, the thing is that the Column "AppName" is only available from Another Query. Each insert into the database is really 2 inserts, one there PdfFileName has a value, and one where column "XmlFileName" has a value. In the row where XmlFileName has a value, the columnn "AppName" also has a value. I need to know this value when I Query the database. But since I need to only select rows where "PdfFileName" has a value, how can I get the AppName value? The column "GUID" is the same for both rows.

The data in the database can look like this...

ID GUID PdfFileName XmlFilename AppName
1 73nB56 73nB56.pdf
2 94mC44 94mC44.xml MyApp
3 93nBc6 93nBc6.pdf
4 93nBc6 93nBc6.xml MyApp2

Is it possible to get a reult like this..
ID GUID PdfFileName XmlFilename AppName
1 73nB56 73nB56.pdf MyApp
3 93nBc6 93nBc6.pdf MyApp2

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-30 : 10:31:35

can you post the other query?

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-09-30 : 10:34:43

Sorry, the other Query does not exsits, I only said so beacuse the AppName is not present in the Query I now have and therfore needs to be found in some way... Do you see what I mean?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-30 : 10:54:20

So...where is the AppName column?

What happens when you run the query you posted?

Also, can you please post the definition of the table dbo.Cards? Ideally, as a CREATE TABLE command

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-09-30 : 11:20:40

The AppName is available from Another row, there are 2 rows for each insert and the key is GUID.

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-09-30 : 11:38:48

If I for example use this Query...


Select * from Cards WHERE GUID = '93nBc6'

I would get 2 rows like this...

ID GUID PdfFileName XmlFilename AppName
3 93nBc6 93nBc6.pdf
4 93nBc6 93nBc6.xml MyApp2

I would only like the to retrieve row id = 3 but with the AppName "MyApp" that is from row id = 4 since they have the same GUID

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-30 : 11:52:02

[code]
select c.*, d.appname
from cards c
join cards d
on c.guid = d.guid and c.id = d.guid -1
[/code]

assuming the row ids always differ by one

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-09-30 : 12:01:00

Sorry, the ID's does not Always differ by one...

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-30 : 13:58:16

OK then, what's the rule? how do you choose the correct row for the appname?

The thing is, you're not giving us enough to go on here. I'm guessing what you might need, but guessing incorrectly.

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-10-01 : 04:28:23

Thanks but after investigating this further I realize I need to take Another approach to this. Thanks for your time though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources