Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Creating one stored proc to bring back results from different tables.

Posted on 2007-09-30
8
Medium Priority
?
163 Views
Last Modified: 2010-03-20
Hi,

I'm designing a website for my football team.

I've done a lot of work with php but am trying to do this one in vb.net 2.0 to give me some well needed pratcise for work.

I want the site to be as dynamic as possible.

The front page will have things like next fixture, last result, recent new etc etc. All of which I will want the club to be able to update themselves via a backend.

my question is:
I want to have one stored procedure that goes off and gets all thefields from the differnt tables etc and return them in one result.

For instance, I have the following query which goes off and gets the next fixture:
SELECT TOP 1 (HOME.TEAMNAME + ' vs ' + AWAY.TEAMNAME) AS NextFixture, MATCHDATE AS NextFixtureDate
            FROM FIXTURES F
            INNER JOIN TEAMS HOME ON HOME.PK_TEAMID = F.HOMETEAM
            INNER JOIN TEAMS AWAY ON AWAY.PK_TEAMID = F.AWAYTEAM

This works fine.

But what I want to be able to do is include that in a bigger query, so that the two fields we return here are returned with other fields.

Something along the lines of:

SELECT FIXTURES.NEXTFIXTURE, FIXTURES.NEXTFIXTUREDATE
FROM
LEFT JOIN
      (SELECT TOP 1 (HOME.TEAMNAME + ' vs ' + AWAY.TEAMNAME) AS NextFixture, MATCHDATE AS NextFixtureDate
            FROM FIXTURES F
            INNER JOIN TEAMS HOME ON HOME.PK_TEAMID = F.HOMETEAM
            INNER JOIN TEAMS AWAY ON AWAY.PK_TEAMID = F.AWAYTEAM
      ) FIXTURES

I'm getting a syntax error:
Msg 156, Level 15, State 1, Procedure SP_POPULATE_INDEX, Line 6
Incorrect syntax near the keyword 'LEFT'.
Msg 102, Level 15, State 1, Procedure SP_POPULATE_INDEX, Line 11
Incorrect syntax near 'FIXTURES'.

Can someone give me any pointers on what I'm trying to do and also advise how i can take this forward to allow me to include smaller 'sub queries'.

I've done some work with sql but not like this before.

Thanks in advance


0
Comment
Question by:scm0sml
  • 4
  • 4
8 Comments
 

Author Comment

by:scm0sml
ID: 19987889
just to advise im using sql server 2005 incase that has any relevance
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19987904
>FROM
>LEFT JOIN
you miss a table name, there. .. or LEFT JOIN is too much?...

0
 

Author Comment

by:scm0sml
ID: 19987943
ok

is what im trying to do viable?

bcos there isnt really one table that im trying to get my data from.

it will be coming from everywhere.

and with my joins.....there isnt really a field that joins everything?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19987961
>is what im trying to do viable?
actually, I don't really see yet WHAT you are trying to do...

>it will be coming from everywhere.
>and with my joins.....there isnt really a field that joins everything?
sorry, but that is "nonsense"...
in other words, you can only have a fixed number of tables involved, and you should know, when writing these queries, how to "join" them (if that is really what you want to do, sometimes the technical word is not what people "mean" by the same word when speaking...)


please explain, with data samples, what you have and what you are trying to get.
0
 

Author Comment

by:scm0sml
ID: 19987985
right ok.

As above i have the small query that returns the NextFixture and NextFixtureDate field.

These are two fields that display on my index page.

They come from fixtures table and teams. These are two fields returned in my data reader.

I would also like other fields returned in mydatareader.....for instance a news paragraph that would come from news table......and a logo url that comes from a setup table....etc etc.....

so to pull a load of data through into one data reader.....put have the data come from many different tables inside my sp but returned as one 'row' of data.

get me?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 19988824
ok,that would be like this:

SELECT *
FROM
      (SELECT TOP 1 (HOME.TEAMNAME + ' vs ' + AWAY.TEAMNAME) AS NextFixture, MATCHDATE AS NextFixtureDate
            FROM FIXTURES F
            INNER JOIN TEAMS HOME ON HOME.PK_TEAMID = F.HOMETEAM
            INNER JOIN TEAMS AWAY ON AWAY.PK_TEAMID = F.AWAYTEAM
      ) FIXTURES
,     ( SELECT TOP 1 paragraph FROM news ... ) NEWS
,     ( SELECT TOP 1 logo_url FROM setup_table ... ) SETUP


1 FROM clause, with several subqueries listed with "," and no where or join clause
of course, assuming that all queries only return 1 row.

an alternative would be to use the NextRecordset method of the datareader:
http://www.paladn.com/articles/ado-dotnet-datareader.htm


say your procedure would be like this:

CREATE PROCEDURE get_page_data
AS
      SELECT TOP 1 (HOME.TEAMNAME + ' vs ' + AWAY.TEAMNAME) AS NextFixture, MATCHDATE AS NextFixtureDate
            FROM FIXTURES F
            INNER JOIN TEAMS HOME ON HOME.PK_TEAMID = F.HOMETEAM
            INNER JOIN TEAMS AWAY ON AWAY.PK_TEAMID = F.AWAYTEAM

       SELECT TOP 1 paragraph FROM news ...

       SELECT TOP 1 logo_url FROM setup_table ...
GO

as you see, 3 queries:

Dim c as SqlCommand = new SqlCommand ( 'get_page_data' , ... )

'run the procedure, the datareader has now the row(s) from the first query
Dim dr as SqlDataReader = c.ExecuteReader

...

'get to the next recordset, which would be here the news
dr.NextRecordset

'get to the next recordset, which would be here the logo url
dr.NextRecordset



hope this gives you a good alternative, also for other jobs.




 




0
 

Author Comment

by:scm0sml
ID: 19989402
yes that looks like exactly what I'm after!!

i'll try it when i get home from work before assigning the points incase i get any more problems.

cheers for that!!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19994036
glad I could help & thanks for the grade
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question