some advice please - 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

General SQL Server Forums

New to SQL Server Programming

some advice please

Author

Topic

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-11-27 : 08:52:18

Hi all, this is just a question on SQL queries really.

This may be a silly question, if so i apologise, but i was wondering how SQL queries operate and process information. What I mean is, for what reason would the following cause my Internet explorer to crash.

SELECT Store_Code, Store_Name, StoreStatus
FROM Stores AS s
LEFT JOIN pgm_store_stories AS ss
ON s.storeID = ss.pss_story_id
WHERE StoreStatus = 'ON'
ORDER BY Store_COde ASC

The above keeps crashing my explorer window. Does this pull all the information from the two tables? or does it simply create a link between the two? and only processes the info when it is called in the code?

Hope I have made myself clear, if not please say and i will try again.

Regards
MG

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-11-27 : 09:01:34

see link

[url]http://www.w3schools.com/sql/sql_join_left.asp[/url]

-----------------------------------------------
Learning something new on SQL Server everyday.

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-11-27 : 10:23:27

Hi,

You're suggesting the problem is to do with the left join?

I just tried to use the Inner join and it returns the same number of records?

Regards
MG

quote:
Originally posted by NeilG

see link

[url]http://www.w3schools.com/sql/sql_join_left.asp[/url]

-----------------------------------------------
Learning something new on SQL Server everyday.

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-11-27 : 11:16:06

I have used 'join' too

Regards
MG

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-11-27 : 12:26:42

running queries shouldn't crash IE. what tool are you using to launch the queries?

the code will run and retrieve data.
if you put "create procedure xxx" before it....running that code would just create the xxx procedure, but not actually run the code.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-27 : 13:58:07

IE?

really?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2009-11-29 : 08:45:17

Have you tried executing the query in SQL Server Management Studio and see whether it crashes?

Jack Vamvas
--------------------
http://www.ITjobfeed.com

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-11-30 : 07:08:16

Hi all,

Just checking back.

I thought I'd leave IE to run and see what happens and after about 3 minutes it didnt crash. It was just taking so long to retrieve all the information from the query. So this was the problem.

I now have refine the query to return only 200 items and not the 990,000 it keeps returning - hence the reason for the constant freezing of IE.

I cant work out why it keeps returning all these items though?
this is the query:

SELECT
Store_Code
FROM Stores AS s
JOIN pgm_store_stories AS ss
on s.Store_Code = ss.pss_store_code
-- JOIN pgm_stories AS st
-- on st.st_section_id = ss.pss_section_id
-- JOIN pgm_action_responses AS acre
-- on st.st_status = acre.ar_status
WHERE StoreStatus = 'ON'

When i select Store_code from the stores tbl, the 200 entries are returned. Yet when i join the tbl to store_stories, the results that are returned go through the roof?

There is a column in tbl_store_stories that is called pss_store_code, and im guessing this is being return too. but would the above not only create a link between the two tables?

Regards
MG

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-11-30 : 07:40:55

Hi all,

Is the reason for the high number of entries being returned be due to my linking two columns that are the same?

Regard
MG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-30 : 09:41:35

quote:
Originally posted by mind_grapes

Hi all,

Is the reason for the high number of entries being returned be due to my linking two columns that are the same?

Regard
MG

nope...may be you had a one to many relation b/w tables. so join will return more rows than actually present in first table.

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-11-30 : 10:51:29

Hi, thanks for the reply

I thought when you make relationships, you can only make them against columns that do not have the same information?

Regards
MG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-30 : 10:54:22

quote:
Originally posted by mind_grapes

Hi, thanks for the reply

I thought when you make relationships, you can only make them against columns that do not have the same information?

Regards
MG

what do you mean by that? whats the use of creating relationship b/w unrelated columns?

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-11-30 : 11:21:35

Hi thanks for post.

Please forgive my lack of knowledge, im new to SQL, stored procedures etc.

Not a lot i admit, I thought it may be due to some sort of duplication or something. I just assumed that was a potential problems was, so thought id ask.

i shall try and sort this out if not will be back for some more help soon.

Regards
MG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-30 : 12:56:06

it would be better if you could explain what difficulty you're experiencing with some sample data for us to see

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-12-01 : 04:36:08

Hi,

I shall try and explain the problem as best as i can. This may be long winded so please bare with me.

I have a number of stores (shops) that are held in one table in the database, these shops are sent "jobs to do" in the form "questions" (held in another table). Once the store(shops) have completed a job they have to reply to the question. This reply is also saved to another table.

I need to produce a list of all the stores that still have outstanding jobs to do together with a list of the questions.

The stores(shops) are held in one table, the question another and the response in another. Things is there are millions of entries, so i need to filter by the date the question was set and the response of the questions (whether the question has been asnwered with a yes completed or no.)

1. tbl stores
> columns: storeID, store_name

linked to:

2.tbl pgm_store_stories
>columns: id, pss_story_id, pss_store_code, pss_section_id

linked to:
3 tbl pgm_stories
>columns: st_id, st_title (of the question), st_date, st_status

linked to 4 and 5:

4. tbl pgm_action_questions
>columns: aq_id, aq_text, aq_story_id, aq_release_date, aq_confirm_date

5. tbl pgm_action_responses
>columns: ar_id, ar_question, ar_answer_id, ar_answer_value, ar_store_code, ar_story_id, ar_confirmdate, ar_status, ar_ok

These tables contain all the information that is important in the question above.

Hope this is what you need. If you need anything else please say.

Regards
MG

mind_grapes
Yak Posting Veteran

71 Posts

Posted - 2009-12-01 : 07:19:18

Hi,

Just to add.

If a store does not reply to a question their names are not inserted in to the table. I think i may need to perform a sub-query?

Regards
MG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources