Newbie question - 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

Newbie question

Author

Topic

posebnaizdaja
Starting Member

3 Posts

Posted - 2011-11-16 : 11:20:38

Hello,

I am new in sql and I have to describe what does this query:


SELECT      rec_header.id,
            rec_header.[name],
            rec_header.description,
            rec_header.version,
            rec_header.product_code,
            typ_article.short_name,
            rec_header.target_instance_id,
            rec_typ_status.[name]
FROM  rec_header
LEFT OUTER JOIN
      typ_article ON rec_header.product_code = typ_article.code
      INNER JOIN
      rec_typ_status ON rec_header.signature_status =rec_typ_status.id
WHERE (rec_header.id = @id OR @id IS NULL)
ORDER BY rec_header.[name]

I would appreciate your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 11:49:25

its returning the details from rec_header table linking it to typ_article and rec_typ_status tables for fetching related fields like name,short_name etc based on passed value for @id parameter. In case @id parameter is passed NULL its bypassing the filter and returning you details of all records in rec_header satisfying above condition. The records returned should have matching detail in rec_typ_status table whereas for typ_article it may or maynot have a match which is why it uses LEFT JOIN. the result is finally sorted on name field from rec_header.

Suggest you to go through concept of joins in below link for more details

http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

posebnaizdaja
Starting Member

3 Posts

Posted - 2011-11-16 : 13:40:02

I don't understand this part at all:

In case @id parameter is passed NULL its bypassing the filter and returning you details of all records in rec_header satisfying above condition. The records returned should have matching detail in rec_typ_status table whereas for typ_article it may or maynot have a match which is why it uses LEFT JOIN.

Can u explain one more time please.
What exactly @id presents?

Thank you very much!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-11-16 : 19:30:48

@id is a variable (a parameter) passed to the routine

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 00:13:48

quote:
Originally posted by posebnaizdaja

I don't understand this part at all:

In case @id parameter is passed NULL its bypassing the filter and returning you details of all records in rec_header satisfying above condition. The records returned should have matching detail in rec_typ_status table whereas for typ_article it may or maynot have a match which is why it uses LEFT JOIN.

Can u explain one more time please.
What exactly @id presents?

Thank you very much!

@id is parameter through you pass value which is used for filtering the resultset based on id field.
the OR condition ensures if you're passing NULL as value for @Id it will ignore the filter and return you all the records.
To understand it, try passing different values for @id (including NULL) and see result obtained in each case

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

posebnaizdaja
Starting Member

3 Posts

Posted - 2011-11-17 : 05:23:59

Visakh16 thank you for detailed explanation!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-17 : 11:03:14

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources