Queries mixing two tables (beginner)

I have following two tables in my database:

cars(regnr, brand, owner, inspected)
owner(ssNr, name, adress)

And i would like a queiry with SQL that is the returning the name of all who is owning a Saab. How would that look?

Something like:

SELECT name,
FROM cars, owner WHERE brand = Saab

??

And how do u return regnr and brand of all cars that has not been inspected since 2006-02-22?

Very thankful for your help!

DanneAs I said in your other thread...
You need some form of primary key/foreign key relationship between the two tables. This allows you to join the table together.
What field in the owners table does the owner field in the cars table correspond to?|||What field in the owners table does the owner field in the cars table correspond to?

my guess:

cars(regnr, brand, owner, inspected)
owner(ssNr, name, adress)|||select owner.*
from owner left join cars on cars.owner = owner.ssNr
where cars.brand="SAAB"

something like that?

thank u for all fast answers :)|||no, not quite, but you're close

sorry, but i cannot continue just giving you the answer, i suspect this is homework and you have to try harder

:)|||yes i gotta specify that only the names shall be written out...
after some googling i came to this conclusion??

select owner.*
from owner left join cars on cars.owner = owner.ssNr
where cars.brand="SAAB" order by owner.name|||select owner.*
from owner left join cars on cars.owner = owner.ssNr
where cars.brand="SAAB"

something like that?

Test it!
What are your expected results and do they match the returned values? :D|||Technically, I think it will return all the records Danne expects, but
the join is not quite right.|||it works quite alright :)

RedNeckGeek, i wonder what is wrong with the join?|||I suggest you look at the different types of joins available in SQL.
Google is a source of endless knowledge ;)|||The following stepwise procedure to construct your SELECT qurey might help with this kind of questions:

Step 1. In which table(s) is the information to be found?
==> answer: in CARS and OWNER
==> put down the following part of your query:
FROM cars, owner
Keep in mind that this asks for a "Cartesian product" of the two tables:
any row of CARS is combined with any row of OWNER.

Step 2. Which horizontal restriction do you want to apply to those rows?
==> answer: (a) only those combinations where the field CARS.OWNER equals the OWNER.SSNR field (since other combinations are meaningless)
(b) and from these, only the rows that have CARS.BRAND = 'Saab'
==> put down the following part of your query:
WHERE cars.owner = owner.ssnr AND cars.brand = 'Saab'

Step 3. Do you want to see individual rows, or summary information?
==> answer: individual information
==> so do NOT put any GROUP BY or HAVING clauses.

Step 4. What fields (attributes) do I want to see from each of the remaining rows?
==> answer: the name of the car owner.
==> put down the following part of your query (before the FROM):
SELECT owner.name

In summary, this gives the following:SELECT owner.name
FROM cars, owner
WHERE cars.owner = owner.ssnr AND cars.brand = 'Saab'

As a final check, a useful rule-of-thumb, make sure that there are n-1 "join conditions" in the WHERE clause, where "n" is the number of tables in the FROM clause.
A join condition links a column from one table to a column of an other table.
Here it's the condition cars.owner = owner.ssnr

With too few join conditions, meaningless combinations will be kept (typically visible by the fact that too many rows show up in the end result).

A better way to write the above query, and one where you cannot forget join conditions, is by using the "... INNER JOIN ... ON ... " syntax, but the ingredients and the steps remain the same:SELECT owner.name
FROM cars INNER JOIN owner ON cars.owner = owner.ssnr
WHERE cars.brand = 'Saab'

Using the same procedure could actually lead to a completely other solution (one using a subquery):

Step 1. I need only information from the table OWNER.
==> FROM owner

Step 2. I want to see only those owners that appear in the CARS table with a certain condition there.
==> WHERE ssnr IN (SELECT owner FROM cars WHERE .... )

Step 3: no summary.
Step 4: just the column NAME.
==> SELECT name

Now we are left with the task to create a list of "owner" values from table CARS:

Step 1: which table(s)?
==> FROM cars
Step 2: which horizontal restriction?
==> WHERE cars.brand = 'Saab'

Putting it all together:SELECT name
FROM owner
WHERE ssnr IN (SELECT owner
FROM cars
WHERE brand = 'Saab')
Additional advantage (or disadvantage?) of the latter solution is that persons having several Saabs will only be shown once, while in the first solution they will be shown as many times as their number of Saabs.
(Adding a "DISTINCT" after SELECT would "solve" that, but at the cost of a must slower query than the one with the subquery.)

qualifying Stored Procedure with owner. Why is it important/

I've been reading some posts that indicate that it is best to invoke a
Stored Procedure with the owner name. Example
exec dbo.MyStoredProc
instead of
exec MyStoredProc
I never really understood why this makes a difference.
I would appreciate it if someone could shed some light on this
Thanks in advanceWell, what about if some user 'user1' have created a procedure which is
owned by 'user1'
If you do "Exec UserProc" you will get an error, you need to use "Exec
user1.UserProc" generally it is advisable to qualify owner name before
database objects.
Things are changed in SQL 2005 where you have to qualify schema name
instead of user name for database objects
chirag shah|||in my database all objects are owned by dbo. I have read that there is a
performance impact by not qualifying the owner of the SP (even in a case
like mine where all objects are owned by dbo)
"c_shah" <shah.chirag@.netzero.net> wrote in message
news:1142951587.510508.143150@.g10g2000cwb.googlegroups.com...
> Well, what about if some user 'user1' have created a procedure which is
> owned by 'user1'
> If you do "Exec UserProc" you will get an error, you need to use "Exec
> user1.UserProc" generally it is advisable to qualify owner name before
> database objects.
> Things are changed in SQL 2005 where you have to qualify schema name
> instead of user name for database objects
> chirag shah
>|||"TJT" <TJT@.nospam.com> wrote in message
news:u0kJDZPTGHA.4864@.TK2MSFTNGP12.phx.gbl...
> in my database all objects are owned by dbo. I have read that there is a
> performance impact by not qualifying the owner of the SP (even in a case
> like mine where all objects are owned by dbo)
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;263889
David|||see
http://www.sqlservercentral.com/columnists/chedgate/worstpracticenotqualifyingobjectswiththeowner.asp
for a discussion of why objects should always be qualified.
"TJT" wrote:
> in my database all objects are owned by dbo. I have read that there is a
> performance impact by not qualifying the owner of the SP (even in a case
> like mine where all objects are owned by dbo)
>
> "c_shah" <shah.chirag@.netzero.net> wrote in message
> news:1142951587.510508.143150@.g10g2000cwb.googlegroups.com...
> > Well, what about if some user 'user1' have created a procedure which is
> > owned by 'user1'
> > If you do "Exec UserProc" you will get an error, you need to use "Exec
> > user1.UserProc" generally it is advisable to qualify owner name before
> > database objects.
> >
> > Things are changed in SQL 2005 where you have to qualify schema name
> > instead of user name for database objects
> >
> > chirag shah
> >
>
>

Qualify query with table name owner

All of the sudden when I do queries on my databases I have to include the ta
ble owner name
Before - Select * from people
After - Select * from carl.people
Don't know what changed... Haven't changed any table owners, config options
for the server or query tool.
Anyone know why?
Thanks,
CarlYou probably don't operate under the user name "carl", quite simply. Execute
SELECT SESSTION_USER and see what it returns.
Btw, it is a good practice to *always* owner-qualify in production code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Carl at pnm" <anonymous@.discussions.microsoft.com> wrote in message
news:EC65D713-D380-4A63-A367-E72CBB18FC6E@.microsoft.com...
> All of the sudden when I do queries on my databases I have to include the
table owner name
> Before - Select * from people
> After - Select * from carl.people
> Don't know what changed... Haven't changed any table owners, config
options for the server or query tool.
> Anyone know why?
> Thanks,
> Carl

q; the owner of the table

Hello,
I need to user full name for the table as seen below.
SELECT @.RowCount = COUNT(*)
FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
T2 may be re-created by different users, how can I get this working for all
users if the creator is not dbo?JIM
You will have to identify an user and then creating dynamic sql if I
understood you properly.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:295BC91E-3F82-4EB8-BFAF-71DCC335FAC5@.microsoft.com...
> Hello,
> I need to user full name for the table as seen below.
> SELECT @.RowCount = COUNT(*)
> FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
> T2 may be re-created by different users, how can I get this working for
> all
> users if the creator is not dbo?
>|||Thanks for the reply, I am trying to select the data whoever creates it, if
the owner is not dbo, my query is not working, owner may be many users since
people drop it an recreate it from time to time.
"Uri Dimant" wrote:

> JIM
> You will have to identify an user and then creating dynamic sql if I
> understood you properly.
>
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:295BC91E-3F82-4EB8-BFAF-71DCC335FAC5@.microsoft.com...
>
>|||JIM
So specify
CREATE TABLE user.TableName (col INT)
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:8EA06ACD-F357-4434-92B2-25B5A47006B3@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply, I am trying to select the data whoever creates it,
> if
> the owner is not dbo, my query is not working, owner may be many users
> since
> people drop it an recreate it from time to time.
> "Uri Dimant" wrote:
>|||JIM.H. wrote:
> Thanks for the reply, I am trying to select the data whoever creates it, i
f
> the owner is not dbo, my query is not working, owner may be many users sin
ce
> people drop it an recreate it from time to time.
> "Uri Dimant" wrote:
Having a table that is repeatedly dropped/created by your users seems
like a bad idea. Why not create a permanent table, owned by dbo, and
have your users TRUNCATE it instead of dropping/creating it? Or better
yet, explain why the need to drop/create exists, and perhaps we can
offer a better idea?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:8EA06ACD-F357-4434-92B2-25B5A47006B3@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply, I am trying to select the data whoever creates it,
> if
> the owner is not dbo, my query is not working, owner may be many users
> since
> people drop it an recreate it from time to time.
> "Uri Dimant" wrote:
>|||Thanks for the reply. I agree however that is the part of the code I do not
have control over, I just need to figure out a way select it based on the
different owner.
"Tracy McKibben" wrote:

> JIM.H. wrote:
> Having a table that is repeatedly dropped/created by your users seems
> like a bad idea. Why not create a permanent table, owned by dbo, and
> have your users TRUNCATE it instead of dropping/creating it? Or better
> yet, explain why the need to drop/create exists, and perhaps we can
> offer a better idea?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

q; the owner of the table

Hello,
I need to user full name for the table as seen below.
SELECT @.RowCount = COUNT(*)
FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
T2 may be re-created by different users, how can I get this working for all
users if the creator is not dbo?JIM
You will have to identify an user and then creating dynamic sql if I
understood you properly.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:295BC91E-3F82-4EB8-BFAF-71DCC335FAC5@.microsoft.com...
> Hello,
> I need to user full name for the table as seen below.
> SELECT @.RowCount = COUNT(*)
> FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
> T2 may be re-created by different users, how can I get this working for
> all
> users if the creator is not dbo?
>|||Thanks for the reply, I am trying to select the data whoever creates it, if
the owner is not dbo, my query is not working, owner may be many users since
people drop it an recreate it from time to time.
"Uri Dimant" wrote:
> JIM
> You will have to identify an user and then creating dynamic sql if I
> understood you properly.
>
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:295BC91E-3F82-4EB8-BFAF-71DCC335FAC5@.microsoft.com...
> > Hello,
> > I need to user full name for the table as seen below.
> >
> > SELECT @.RowCount = COUNT(*)
> > FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
> >
> > T2 may be re-created by different users, how can I get this working for
> > all
> > users if the creator is not dbo?
> >
>
>|||JIM
So specify
CREATE TABLE user.TableName (col INT)
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:8EA06ACD-F357-4434-92B2-25B5A47006B3@.microsoft.com...
> Thanks for the reply, I am trying to select the data whoever creates it,
> if
> the owner is not dbo, my query is not working, owner may be many users
> since
> people drop it an recreate it from time to time.
> "Uri Dimant" wrote:
>> JIM
>> You will have to identify an user and then creating dynamic sql if I
>> understood you properly.
>>
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:295BC91E-3F82-4EB8-BFAF-71DCC335FAC5@.microsoft.com...
>> > Hello,
>> > I need to user full name for the table as seen below.
>> >
>> > SELECT @.RowCount = COUNT(*)
>> > FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
>> >
>> > T2 may be re-created by different users, how can I get this working for
>> > all
>> > users if the creator is not dbo?
>> >
>>|||JIM.H. wrote:
> Thanks for the reply, I am trying to select the data whoever creates it, if
> the owner is not dbo, my query is not working, owner may be many users since
> people drop it an recreate it from time to time.
> "Uri Dimant" wrote:
Having a table that is repeatedly dropped/created by your users seems
like a bad idea. Why not create a permanent table, owned by dbo, and
have your users TRUNCATE it instead of dropping/creating it? Or better
yet, explain why the need to drop/create exists, and perhaps we can
offer a better idea?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:8EA06ACD-F357-4434-92B2-25B5A47006B3@.microsoft.com...
> Thanks for the reply, I am trying to select the data whoever creates it,
> if
> the owner is not dbo, my query is not working, owner may be many users
> since
> people drop it an recreate it from time to time.
> "Uri Dimant" wrote:
>> JIM
>> You will have to identify an user and then creating dynamic sql if I
>> understood you properly.
>>
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:295BC91E-3F82-4EB8-BFAF-71DCC335FAC5@.microsoft.com...
>> > Hello,
>> > I need to user full name for the table as seen below.
>> >
>> > SELECT @.RowCount = COUNT(*)
>> > FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
>> >
>> > T2 may be re-created by different users, how can I get this working for
>> > all
>> > users if the creator is not dbo?
>> >
>>|||Thanks for the reply. I agree however that is the part of the code I do not
have control over, I just need to figure out a way select it based on the
different owner.
"Tracy McKibben" wrote:
> JIM.H. wrote:
> > Thanks for the reply, I am trying to select the data whoever creates it, if
> > the owner is not dbo, my query is not working, owner may be many users since
> > people drop it an recreate it from time to time.
> >
> > "Uri Dimant" wrote:
> Having a table that is repeatedly dropped/created by your users seems
> like a bad idea. Why not create a permanent table, owned by dbo, and
> have your users TRUNCATE it instead of dropping/creating it? Or better
> yet, explain why the need to drop/create exists, and perhaps we can
> offer a better idea?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>