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.)
