|
|
Hey guys,
I'm trying to figure something out with this query I'm writing up.
The schema of the research DB is as follows:
Department(DeptNum, Descrip, Instname, DeptName, State, Postcode)
Academic(AcNum, DeptNum, FamName, GiveName, Initials, Title)
Paper(PaNum, Title)
Author(*PaNum, *AcNum)
Field(FieldNum, ID, Title)
Interest(*FieldNum, *AcNum, Descrip)
Basically the question I'm trying to answer is:
Which academics have published a paper with at least one academic from Tasmania? Return their academic number, given name, family name, and department number.
(Hints: Use LIKE '%Tas%' to build the query in stages. If a paper is published by one academic and he (she) is from Tasmanian, do not output the academic for this reason. If a paper is published by two or more academics and at least one of them are from Tas, then output them.)
And so far after one week of doing my head in I have this:
select acnum,deptnum,famname,givename
from academic
where deptnum = any (select DeptNum
from department
where State LIKE '%Tas%'
or State LIKE '%TAS%');
I'm trying to select all the books with authors > 1 then elminiate all that have no tasmanians but I'm not sure how to write it up in sql. I was hoping someone can help me.
I also wanted to know which word or sign to use for "same" like if i want to search for authors with the same family name.
Thanks in advance 
|
|
|
|
|
Hi Diana
Sounds like homework - so I'll try to walk you through the solution, rather than just give it to you on a plate:
The way that I would approach this would be to build a select statement that returns all of the "Authors" who reside in Tasmania (you will need to join between 3 tables).
I would then substitute that select statement into the from-clause of the following:
select distinct Academic.AcNum, Academic.GiveName, Academic.FamName,
Academic.DeptNum
from (
--Your tasmanian-authors select statement goes here.
select TasmanianAuthor.PaNum, TasmanianAuthor.AcNum
from Author as TasmanianAuthor
inner join ...
where ...
) X
inner join Author
on Author.PaNum = X.PaNum
and Author.AcNum <> X.AcNum
inner join Academic
on Academic.AcNum = Author.AcNum
order by Academic.AcNumI know that embedding a select-statement within a from-clause is supported by at-least SQL-Server, Oracle, and DB2. If your database doesn't support this technique then try looking-up "correlated sub-queries" to find a possible alternative way of achieving the same result.
The "distinct" clause will be required where an academic has authored several papers, or if a paper has been authored by more than 2 academics.
If you want to learn more about SQL programming (and you should because SQL allows you to do some reeeeally powerful stuff) then there are plenty of good articles on the web or books in the bookshops. My own favorite is Joe Celko's SQL for Smarties.
Hope that helps.
Andy
|
|
|
|
|
Thank you so much Andy
It worked great. Now I'm having issues with displaying people who have the same family name. I tried both ways but my query just times out.
Is there something wrong with this query:
Select famname,givename
From academic
Where famname=famname;
I think I'm gonna go track down that book. I really do want to learn SQL.
Thanks so much for your help
Diana
|
|
|
|
|
Satips wrote: Then how will you program and create query Diana.
I guess because she said she wanted to learn SQL.
|
|
|
|
|
Hi Diana
Academics with the same family name would be:
select Academic.AcNum, Academic.FamName, Academic.GivenName, Academic.DeptNum
from Academic
where Academic.FamName in (
select FamName
from Academic
group by FamName
having count(*) > 1)
order by Academic.AcNum The sub-query gives you a list of all of the family names where there are two-or-more researchers.
This type of query is often used to identify duplicate records in tables.
Regards
Andy
|
|
|
|
|
Thanks Andy
You're a Star...
|
|
|
|
|
I created a little query to assist our support guy in a workaround. The task was to create a backup table for all vendors not flagged for VAT, then flag all vendors for VAT, run an export to Pastel, then use the backup table to reset all the originally non-VAT vendors.
My manager called me over because step 2 wouldn't execute, it complained that the table VendorBak doesn't exist. When he added the create table statement, step 1 and 2 worked. How can the select into work when the table exists? Is there some strange condition that allows this?
Declare @stepid int
set @stepid = 1 --change to 2 after you have run the export
if @stepid = 1
create table vendorbak (DCLink int, Account nvarchar(1000), Name nvarchar(1000))
select DCLink, Account, Name into VendorBak from Vendor where CT = 0
update Vendor set CT = 1
if @stepid = 2
update Vendor set CT = 0 from VendorBak vb where Vendor.DCLink = vb.DCLink
drop table vendorbak
|
|
|
|
|
The "select .... into" statement creates its own table.
You should either remove your explicit "create table" statement, or replace your "select .... into" statement with an "insert .... select" statement.
|
|
|
|
|
IF statements only control the next statement. They do not act as a block, despite your indentation. To control more than one statement, use BEGIN/END.
As a result, your code really looks like this:
Declare @stepid int
set @stepid = 1 --change to 2 after you have run the export
if @stepid = 1
create table vendorbak (DCLink int, Account nvarchar(1000), Name nvarchar(1000))
select DCLink, Account, Name into VendorBak from Vendor where CT = 0
update Vendor set CT = 1
if @stepid = 2
update Vendor set CT = 0 from VendorBak vb where Vendor.DCLink = vb.DCLink
drop table vendorbakThat means that when @stepid is 1, it will create the table, then do the SELECT INTO, then update, then drop the table.
|
|
|
|
|
Hi,
I have a T-SQL script file which i want to run using C#. Can any body please tell me how can i do it?
Regards,
Wasif.
|
|
|
|
|
Running SQL Scripts with a .NET Application[^]
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
|
That wasn't on the page I linked to. Which page did that link come from?
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
It was not the link what you referred. I bookmarked this link long back. And yesterday I tried to get it again and resulted in 404. I just thought of intimating you
|
|
|
|
|
Navaneeth. wrote: I bookmarked this link long back. And yesterday I tried to get it again and resulted in 404.
Ah... That website fell off the internet. The new link is: Types of Join[^]
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
Hi, I need to retrieve the column info only from a table that i created in the DB. Though I know it can be done using sys.objects/sys.columns, i do not know how to form the query. please help.
|
|
|
|
|
Retrieving column names of a table
There are basically 3 approaches to this problem:
1.Use the sp_help procedure to get extended information about a
database object. By database object, we mean a table, view etc.
2.You can use the following select statement to retrieve a table’s columns:
SELECT TOP 0 * FROM table_name
3.The third approach, which I personally prefer, is using the schema object of SQL Server. For example, to retrieve the column names of Authors table, you can
use the following SELECT statement:
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME 'authors'
Which results in the following result set:
column_name<br />
----------------------------<br />
au_id<br />
au_lname<br />
au_fname<br />
phone<br />
address<br />
city<br />
state<br />
zip<br />
contract<br />
(9 row(s) affected)
Tirtha
Do not go where the path may lead, go instead where there is no path and leave a trail.
Author: Ralph Waldo Emerson (1803-82), American writer, philosopher, poet, essayist
|
|
|
|
|
I have a data table and I want to find the row in the data table that contains the string "Dan" in the third column of the data table but I am unsure how to do this.
I have looked at .find but it uses the PK, which is set in the original database but not in the data table. Is it possible to bring the PK and the field names from the database to the data table?
Thanks,
Dan
|
|
|
|
|
Hey
Can you not just iterate through each row checking the content of the third column?
Or maybe look at this:
http://msdn2.microsoft.com/en-us/library/system.data.datatable.primarykey(VS.80).aspx
Try looknig at datasets in msdn also they may be more suitable for the job as I am sure they retain the schema.
Hope this is of use
Dan
|
|
|
|
|
|
That article says if it's not being returned it's because you've done something wrong!
Whats your code in the client for getting the output param?
|
|
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
