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?
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-09-30 : 12:01:00
Sorry, the ID's does not Always differ by one...
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -