Results 1 to 1 of 1
  1. #1
    Join Date
    Mar 2002
    Location
    Pontypridd
    Posts
    1

    Post Unanswered: Newbie needs help over Query!

    Thank you for your help!

    Hello there,

    I am pretty new to all of this SQL query language, and have found a lot of difficulty in getting this to work. I can gain the results that I require by using a VIEW statement, but I am unable to create a single statement that achieves what I wish.

    What I need to know is how to retrieve from a four-table join the names, addresses and post codes of customers who have hired every make of car. There are three individual makes, which only one customer has hired. All of the tables are within the attached Test.zip file, in a MS Access '97 database, because i tried to get a visual idea of how to solve this :-) Unfortunately it didn't help much...

    Any help/assistance/solutions that anyone can offer, would be very much appreciated!


    Thank you,

    Dafydd



    Below are my workings out, and as you can see, they're a mess


    ************************************************** *********

    select distinct customer.name, customer.address, customer.post_code, Count(car_type.maker)
    from customer, car_type, hiring, car
    where customer.acc_no=hiring.cust_acc and
    hiring.regno=car.regno and car_type.model = car.model
    order by car_type.maker


    group by car_type.maker
    having count(car_type.maker)>2;



    select distinct count(car_type.maker), customer.name, customer.address, customer.post_code
    from customer, car_type, hiring, car
    where customer.acc_no=hiring.cust_acc and
    hiring.regno=car.regno
    group by customer.name, hiring.regno
    having Count(car_type.maker)>2;


    select name, address, post_code
    from customer, hiring
    where customer.acc_no=hiring.cust_acc
    union
    select count(maker)
    from car, car_type, hiring
    where hiring.regno=car.regno

    select count(customer.name), customer.address, customer.post_code
    from customer, car, hiring
    where customer.acc_no=hiring.cust_acc and hiring.regno=car.regno
    group by customer.name
    having count(customer.name)>2

    select name, address, post_code
    from customer
    where acc_no in
    (select acc_no from customer, hiring where cust_acc=acc_no);


    select name, address, post_code
    from customer
    where acc_no in
    (select acc_no from customer, hiring, where cust_acc=acc_no);


    select name, address, post_code
    from customer
    where acc_no in
    (select acc_no from customer, hiring
    where acc_no=cust_acc
    and hiring.regno in
    (select hiring.regno
    from hiring, car
    where hiring.regno=car.regno));

    Lists 8 distinct customers!



    select distinct name, address, post_code, maker, count(cust_acc)
    from customer, car_type
    where acc_no in
    (select acc_no from customer, hiring
    where acc_no=cust_acc
    and hiring.regno in
    (select hiring.regno
    from hiring, car
    where hiring.regno=car.regno))
    and maker in
    (select maker
    from car_type, car
    where car_type.model=car.model
    and car_type.esize=car.esize)
    and count(cust_acc) > 2;



    select name, address, post_code, maker
    from customer, car_type
    where cust_acc

    SORT OF WORKS... But Doubles original rows... :-(


    select distinct customer.name, customer.address,
    customer.post_code, car_type.maker
    from customer, car_type, hiring, car
    where customer.acc_no=hiring.cust_acc and
    hiring.regno=car.regno and car_type.model = car.model
    order by customer.name;






    SELECT DISTINCT customer.name, customer.address, customer.post_code, car_type.maker, Customer.acc_no
    FROM customer, car_type, hiring, car
    WHERE customer.acc_no=hiring.cust_acc and car_type.model = car.model
    and hiring.cust_acc in
    (select hiring.cust_acc from hiring, car_type, car where hiring.regno=car.regno and car_type.maker = 'Vauxhall')
    and hiring.cust_acc in
    (select hiring.cust_acc from hiring, car_type, car where hiring.regno=car.regno and car_type.maker = 'Ford')
    and hiring.cust_acc in
    (select hiring.cust_acc from hiring, car_type, car where hiring.regno=car.regno and car_type.maker = 'Jaguar')
    ORDER BY customer.acc_no;



    select distinct customer.name, customer.address, customer.post_code, car_type.maker
    from customer, car_type, hiring, car
    where customer.acc_no=hiring.cust_acc
    and hiring.regno=car.regno
    and car.esize=car_type.esize
    and car.model=car_type.model
    and customer.name='Mr F Curlew'
    order by customer.name, car_type.maker;



    select distinct customer.name, customer.address, customer.post_code, car_type.maker
    from customer, car_type, hiring, car
    where customer.acc_no=hiring.cust_acc
    and hiring.regno=car.regno
    and car.esize=car_type.esize
    and car.model=car_type.model
    and car_type.maker in
    (select car_type.maker
    from customer, hiring, car_type, car
    where customer.acc_no=hiring.cust_acc
    and hiring.regno=car.regno
    and car_type.maker like 'F%'
    and car_type.maker like 'J%'
    and car_type.maker like 'V%')
    order by customer.name, car_type.maker;


    select distinct customer.name, customer.address, customer.post_code, car_type.maker
    from customer, car_type, hiring, car
    where customer.acc_no=hiring.cust_acc
    and hiring.regno=car.regno
    and car.esize=car_type.esize
    and car.model=car_type.model
    and car_type.maker like 'F%'
    and car_type.maker like 'J%'
    and car_type.maker like 'V%'
    order by customer.name, car_type.maker;


    select distinct customer.name,
    customer.address, customer.post_code,
    car_type.maker
    from customer, car_type, hiring, car
    where customer.acc_no=hiring.cust_acc
    and hiring.regno=car.regno
    and car.esize=car_type.esize
    and car.model=car_type.model
    and car_type.maker like 'F%'
    and car_type.maker like 'J%'
    and car_type.maker like 'V%'
    order by customer.name, car_type.maker;


    ************************************************

    create view Hire_Of_Makes(name, address, post_code, make) as
    select distinct customer.name, customer.address,
    customer.post_code, car_type.maker
    from customer, car_type, hiring, car
    where customer.acc_no=hiring.cust_acc and
    hiring.regno=car.regno and car_type.model = car.model
    order by customer.name;

    ************************************************
    Creates a VIEW of the information I wish to search on.

    select count(name) No_Of_Makes_Hired, name
    from Hire_Of_Makes
    group by name
    having count(name)>2;

    Creates a list of all customers who have 3 or more makes Hired.


    select distinct customer.name, customer.address,
    customer.post_code, car_type.maker
    from customer, car_type, hiring, car
    where customer.acc_no=hiring.cust_acc and
    hiring.regno=car.regno and car_type.model = car.model
    and customer.name in
    (select name
    from Hire_Of_Makes
    group by name
    having count(name)>2)
    order by customer.name;

    Lists the initial VIEW query, with the correct amount of distinct customers.



    select distinct customer.name, customer.address,
    customer.post_code, car_type.maker
    from customer, car_type, hiring, car
    where customer.acc_no=hiring.cust_acc and
    hiring.regno=car.regno and car_type.model = car.model
    and customer.name in
    (select name
    from customer, car_type, hiring, car
    where customer.acc_no=hiring.cust_acc and
    hiring.regno=car.regno and car_type.model = car.model
    group by name
    having count(name)>2)
    order by customer.name;

    ******

    SELECT DISTINCT customer.name, customer.address, customer.post_code, car_type.maker
    FROM customer, car_type, hiring, car
    WHERE customer.acc_no=hiring.cust_acc and
    hiring.regno=car.regno and car_type.model = car.model
    and customer.name in
    (select name
    from customer, car_type, hiring, car
    where customer.acc_no=hiring.cust_acc and
    hiring.regno=car.regno and car_type.model = car.model
    and name in
    ((create view Hire_Of_Makes(name, address, post_code, make) as
    select distinct customer.name, customer.address,
    customer.post_code, car_type.maker
    from customer, car_type, hiring, car
    where customer.acc_no=hiring.cust_acc and
    hiring.regno=car.regno and car_type.model = car.model)
    select name
    from Hire_Of_Makes
    group by name
    having count(name)>2))
    ORDER BY customer.name;

    ******
    Tries to create a view AND do the select staments needed.





    select distinct cu.name, cu.address, cu.post_code, ct.maker
    from customer cu, car_type ct, hiring hi, car ca
    where cu.acc_no=hi.cust_acc
    and hi.regno=ca.regno
    and ct.model=ca.model
    and ct.esize=ca.esize
    and cu.name in
    (select cu.name
    from customer c, car_type ct, hiring hi, car ca
    where cu.acc_no=hi.cust_acc
    and hi.regno=ca.regno
    and ct.model=ca.model
    and cu.name in
    (select c.name
    from customer c
    group by name
    having count(name)>2))
    order by cu.name
    Attached Files Attached Files

Posting Permissions

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