Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2007
    Posts
    6

    Unanswered: 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!

    Danne

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    As 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?
    Inspiration Through Fermentation

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by RedNeckGeek
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2007
    Posts
    6
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2007
    Posts
    6
    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

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by danneny
    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?
    George
    Home | Blog

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Technically, I think it will return all the records Danne expects, but
    the join is not quite right.
    Inspiration Through Fermentation

  9. #9
    Join Date
    Feb 2007
    Posts
    6
    it works quite alright

    RedNeckGeek, i wonder what is wrong with the join?

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I suggest you look at the different types of joins available in SQL.
    Google is a source of endless knowledge
    George
    Home | Blog

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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:
    Code:
    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:
    Code:
    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:
    Code:
    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.)
    Last edited by Peter.Vanroose; 02-28-07 at 16:30.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •