Easy Select 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

SQL Server 2000 Forums

SQL Server Development (2000)

Easy Select Question

Author

Topic

neutcomp
Posting Yak Master

111 Posts

Posted - 2005-05-30 : 12:07:39

I have this query.

SELECT pe_first_name, ph_name FROM pe_person, ph_photo WHERE ph_id = ph_pe_id

This works fine for people how have a picture but If the have no picture the are not listed. Is there a way to get back always the info but if there is no record I get NULL for the ph_name?

Thanxx
Bjorn

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-30 : 12:22:09

Use a left left join.
It is a good habit to prefix the columns with the table name or alias.
Learn the ansi syntax for joins.
The naming convention You used with prefixes for table and column names is very confusing,
just give the tables and columns representative names.

SELECT
	pe.pe_first_name
	,ph.ph_name
FROM
	pe_person pe
	LEFT OUTER JOIN ph_photo ph
		ON ph.ph_id = ph.pe_pe_id

rockmoose

neutcomp
Posting Yak Master

111 Posts

Posted - 2005-05-30 : 13:26:31

Thanxx it works!!
But its just NOT confusing, I start a colum name always with the two first letters of the table name.

pe_person
pe_id
pe_first_name
pe_address

ph_photo
ph_id
ph_pe_id
ph_name

So If I use joins I dont have to use the photo AS ph, person AS pe because the ph_pe_id and pe_id are always differt by name.

Cya
Bjorn

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-30 : 15:24:18

Maybe it is not confusing for You

rockmoose

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-05-31 : 01:15:03

Take it from the rest of the world...it's confusing. We understand the syntax you are using; it's still confusing.

HTH

=================================================================
The surest way to corrupt a youth is to instruct him to hold in higher esteem those who think alike than those who think differently. -Friedrich Nietzsche, philosopher (1844-1900)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-31 : 03:48:59

Maybe it's Dutch notation ?

rockmoose

neutcomp
Posting Yak Master

111 Posts

Posted - 2005-05-31 : 05:01:05

Ok, always in for some syntax lessons. Where can I find the "Learn the ansi syntax for joins" lessons?

Thanxx
Bjorn

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-31 : 08:22:58

In BOL under the topic: "FROM clause" -> "FROM clause(described)"
See Examples C-G in the remarks section.

After getting used to the syntax You will find it more readable.
It is easier maintain and to add/remove tables from the query.
And You keep the "join logic" apart from the "filtering logic".
If You are writing OUTER JOINS it is the only syntax that makes sense.

SELECT
	<column_list>
FROM
	TableX AS x
	<join_type> JOIN TableY AS y
		ON x.colA = y.colE
		AND x.colB = y.colF	<--- multiple join criteria possible
	<join_type> JOIN TableZ AS z
		ON y.ColA = z.ColF			<--- TableY + TableZ
		AND x.ColC BETWEEN z.Start AND z.End	<--- non equi join
WHERE
	<filter_criteria>


<join_type>:
INNER
LEFT OUTER
RIGHT OUTER (use left joins instead of right joins)
FULL OUTER
CROSS

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-31 : 09:03:15

quote:
Originally posted by neutcomp

Thanxx it works!!
But its just NOT confusing, I start a colum name always with the two first letters of the table name.

pe_person
pe_id
pe_first_name
pe_address

ph_photo
ph_id
ph_pe_id
ph_name

So If I use joins I dont have to use the photo AS ph, person AS pe because the ph_pe_id and pe_id are always differt by name.

Cya
Bjorn

That's redundant -- the column itself belongs to the table, so there is no need to repeat that information. And in a SELECT, prefixing all columns and alias's with the table name (or derived table alias) keeps things very clear, especially when the columns come from other SELECT statemetns and/or Views, in which case your column prefixes no longer make any sense and only confuse things further.

- Jeff

neutcomp
Posting Yak Master

111 Posts

Posted - 2005-05-31 : 09:53:40

Ok, I dont know If you only use SQL or also programming in PHP? But If you have more query's from several databases and you got.
id, id, id, id you dont know If you say
echo $row_1['id']; //What id?

What id you are using, and if you have ph_id, pe_id, ca_id, st_id you can relate the things to, photo, person, car, study etc..
Thats why I always use the prefix before every colum.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-05-31 : 11:24:01

You can always tell a Dutchman....you just can't tell him much. ;-)

HTH

=================================================================
The surest way to corrupt a youth is to instruct him to hold in higher esteem those who think alike than those who think differently. -Friedrich Nietzsche, philosopher (1844-1900)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-31 : 11:37:01

>>What id you are using, and if you have ph_id, pe_id, ca_id, st_id you can relate the things to, photo, person, car, study etc..

Then the column names should be StudyID, PersonID, CarID -- isn't that a little more readable and intuitive? And also remember that in general it is a bad idea to get into the habit of simply adding a column called "ID" (with or without a prefix) to every table to serve as an identity primary key. Many tables have natural keys already in the data itself.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-31 : 12:02:54

quote:
Originally posted by jsmith8858

>>And also remember that in general it is a bad idea to get into the habit of simply adding a column called "ID"

Unless you're the SQL Server catalog, then it's OK

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

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-31 : 14:11:02

quote:
Unless you're the SQL Server catalog, then it's OK

Funny,

, (it's almost? always the id of an object in sysobjects)
That's different than adding id, pe_id, gn_id, gu_id <----
to every table.

Bjorn we are waiting for the "how do I remove duplicates ?" question.

rockmoose

neutcomp
Posting Yak Master

111 Posts

Posted - 2005-05-31 : 14:57:30

quote:
Originally posted by rockmoose

[quote]Bjorn we are waiting for the "how do I remove duplicates ?" question.

rockmoose

I don't understand your question? What do you want to know?
Can you explain?

Thanxx
Bjorn

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-31 : 15:42:14

Just stupid, sorry about that Bjorn.
The point was that (only) an ID (identity/guid//) as PK does not protect the table from duplicate data.

Anyway, the naming standard with prefix before each column is completely redundant.
You cannot reference a database column without the context of a database table,
so the table is always known.

What if You ever rename a table, rename all the columns as well ?

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources