I added a join table which was left out of the structure. In order to
populate it with the key fields of the company and car tables this is
the SQL I arrived at. My question is this right?

INSERT INTO Company_Car_jn(car_id,comp_id)
SELECT comp_id from Internal_Company,
car_id from Internal_Car,
WHERE Internal_Company.comp_id = Internal_Car.comp_id;

tia,

This does not look like a valid SQL statement. Try this:

INSERT INTO Company_Car_jn (car_id, comp_id)
SELECT B.car_id, A.comp_id
FROM Internal_Company AS A
JOIN Internal_Car AS B
ON A.comp_id = B.comp_id;

If the JOIN can produce duplicate pair values then add DISTINCT to the
SELECT statement.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

No, this syntax is not correct. Plus, you would have but company ID's in
the car_ID column, and vice versa.

But what is the purpose of this "join table" if you can already derive this
information in a query?

INSERT INTO Company_Car_jn
(
Car_ID,
Comp_ID
)
SELECT
car.Car_ID,
company.Comp_ID
FROM
Internal_Car AS car
INNER JOIN
Internal_Company AS company
ON
car.comp_id = company.comp_id;

Who is going to maintain this superfluous join table? Are you really going
to update the whole table every time a single row is touched in either of
the tables involved? Why not use a view instead of storing redundant data
in another table?







On 6/16/08 2:15 PM, in article
80f2f737-4136-4a98-809a-769311095846...oglegroups.com, "Janis
Rough" <jlrough@yahoo.com> wrote:

> I added a join table which was left out of the structure. In order to
> populate it with the key fields of the company and car tables this is
> the SQL I arrived at. My question is this right?
>
> INSERT INTO Company_Car_jn(car_id,comp_id)
> SELECT comp_id from Internal_Company,
> car_id from Internal_Car,
> WHERE Internal_Company.comp_id = Internal_Car.comp_id;
>
> tia,

On Jun 16, 11:24 am, "Aaron Bertrand [SQL Server MVP]"
<ten....@dnartreb.noraa> wrote:
> No, this syntax is not correct. Plus, you would have but company ID's in
> the car_ID column, and vice versa.
>
> But what is the purpose of this "join table" if you can already derive this
> information in a query?
>
> INSERT INTO Company_Car_jn
> (
> Car_ID,
> Comp_ID
> )
> SELECT
> car.Car_ID,
> company.Comp_ID
> FROM
> Internal_Car AS car
> INNER JOIN
> Internal_Company AS company
> ON
> car.comp_id = company.comp_id;
>
> Who is going to maintain this superfluous join table? Are you really going
> to update the whole table every time a single row is touched in either of
> the tables involved? Why not use a view instead of storing redundant data
> in another table?
>
> On 6/16/08 2:15 PM, in article
> 80f2f737-4136-4a98-809a-769311095...@z24g2000prf.googlegroups.com, "Janis
>
> Rough" <jlro...@yahoo.com> wrote:
> > I added a join table which was left out of the structure. In order to
> > populate it with the key fields of the company and car tables this is
> > the SQL I arrived at. My question is this right?
>
> > INSERT INTO Company_Car_jn(car_id,comp_id)
> > SELECT comp_id from Internal_Company,
> > car_id from Internal_Car,
> > WHERE Internal_Company.comp_id = Internal_Car.comp_id;
>
> > tia,

Well the purpose is a report. Right now I have a complicated SQL query
report that shows each car owner, and the rate and who it is leased
to.
The problem is one car can be owned by more than one owners. And one
owner can have more than one car which is a many to many. This report
gives the totals of car rental rates per owner but the problem is it
only shows the current cars owners. It does not show the cars numbers
or rates that were to previous owners. I am trying to fix it by
adding a join table. So you are saying I can create a view of the 3
tables and use that as a table to create the report instead of
changing the structure?

It sounds like that might work.

thanks,

On Jun 16, 11:24 am, "Aaron Bertrand [SQL Server MVP]"
<ten....@dnartreb.noraa> wrote:
> No, this syntax is not correct. Plus, you would have but company ID's in
> the car_ID column, and vice versa.
>
> But what is the purpose of this "join table" if you can already derive this
> information in a query?
>
> INSERT INTO Company_Car_jn
> (
> Car_ID,
> Comp_ID
> )
> SELECT
> car.Car_ID,
> company.Comp_ID
> FROM
> Internal_Car AS car
> INNER JOIN
> Internal_Company AS company
> ON
> car.comp_id = company.comp_id;
>
> Who is going to maintain this superfluous join table? Are you really going
> to update the whole table every time a single row is touched in either of
> the tables involved? Why not use a view instead of storing redundant data
> in another table?
>
> On 6/16/08 2:15 PM, in article
> 80f2f737-4136-4a98-809a-769311095...@z24g2000prf.googlegroups.com, "Janis
>
> Rough" <jlro...@yahoo.com> wrote:
> > I added a join table which was left out of the structure. In order to
> > populate it with the key fields of the company and car tables this is
> > the SQL I arrived at. My question is this right?
>
> > INSERT INTO Company_Car_jn(car_id,comp_id)
> > SELECT comp_id from Internal_Company,
> > car_id from Internal_Car,
> > WHERE Internal_Company.comp_id = Internal_Car.comp_id;
>
> > tia,

I will see if I can figure out how to create a view to make the join
table from the other two tables. The purpose is to create a report
that
shows the car owners and the lessee's of each car. Right now without
the join file the records only reflect the current owners
not the cars that were owned by previous owners because the join file
is missing.

On Jun 16, 11:24 am, "Aaron Bertrand [SQL Server MVP]"
<ten....@dnartreb.noraa> wrote:
> No, this syntax is not correct. Plus, you would have but company ID's in
> the car_ID column, and vice versa.
>
> But what is the purpose of this "join table" if you can already derive this
> information in a query?
>
> INSERT INTO Company_Car_jn
> (
> Car_ID,
> Comp_ID
> )
> SELECT
> car.Car_ID,
> company.Comp_ID
> FROM
> Internal_Car AS car
> INNER JOIN
> Internal_Company AS company
> ON
> car.comp_id = company.comp_id;
>
> Who is going to maintain this superfluous join table? Are you really going
> to update the whole table every time a single row is touched in either of
> the tables involved? Why not use a view instead of storing redundant data
> in another table?
>
> On 6/16/08 2:15 PM, in article
> 80f2f737-4136-4a98-809a-769311095...@z24g2000prf.googlegroups.com, "Janis
>
> Rough" <jlro...@yahoo.com> wrote:
> > I added a join table which was left out of the structure. In order to
> > populate it with the key fields of the company and car tables this is
> > the SQL I arrived at. My question is this right?
>
> > INSERT INTO Company_Car_jn(car_id,comp_id)
> > SELECT comp_id from Internal_Company,
> > car_id from Internal_Car,
> > WHERE Internal_Company.comp_id = Internal_Car.comp_id;
>
> > tia,

II definitely need the join file. I have a report that shows each car
by owner and lessee but it only shows cars by current owners not
previous owners. have a question on how to create the view. Do I
create the view with only the join table? Then I can use that join
table view to select all
owners of one car or all lessee's of one owner etc. etc? Or do I
create the view with all three tables?

> II definitely need the join file. I have a report that shows each car
> by owner and lessee but it only shows cars by current owners not
> previous owners. have a question on how to create the view. Do I
> create the view with only the join table? Then I can use that join
> table view to select all
> owners of one car or all lessee's of one owner etc. etc? Or do I
> create the view with all three tables?

Janis, I don't want to offend you, but I think you need to step back and
start at a more basic step. It seems you are not even clear on the
differences between a view and a table?

Here you are trying to create a table that has this data:

SELECT
car.Car_ID,
company.Comp_ID
FROM
Internal_Car AS car
INNER JOIN
Internal_Company AS company
ON
car.comp_id = company.comp_id;

Presumably, you will later reference this table in some three-table join.

Why not create a view using this exact query:

CREATE VIEW dbo.View_Company_Car
AS
SELECT
car.Car_ID,
company.Comp_ID
FROM
Internal_Car AS car
INNER JOIN
Internal_Company AS company
ON
car.comp_id = company.comp_id;

Now you can reference this view in your three-table join instead.

Still, I think you need to take a very elemental course on database
structure and design... Or do a lot more reading, or something. Again, not
trying to be offensive, but you are trying to do things that don't make
sense because a real basic comprehension is missing...

On Jun 16, 12:28 pm, "Aaron Bertrand [SQL Server MVP]"
<ten....@dnartreb.noraa> wrote:
> > II definitely need the join file. I have a report that shows each car
> > by owner and lessee but it only shows cars by current owners not
> > previous owners. have a question on how to create the view. Do I
> > create the view with only the join table? Then I can use that join
> > table view to select all
> > owners of one car or all lessee's of one owner etc. etc? Or do I
> > create the view with all three tables?
>
> Janis, I don't want to offend you, but I think you need to step back and
> start at a more basic step. It seems you are not even clear on the
> differences between a view and a table?
>
> Here you are trying to create a table that has this data:
>
> SELECT
> car.Car_ID,
> company.Comp_ID
> FROM
> Internal_Car AS car
> INNER JOIN
> Internal_Company AS company
> ON
> car.comp_id = company.comp_id;
>
> Presumably, you will later reference this table in some three-table join.
>
> Why not create a view using this exact query:
>
> CREATE VIEW dbo.View_Company_Car
> AS
> SELECT
> car.Car_ID,
> company.Comp_ID
> FROM
> Internal_Car AS car
> INNER JOIN
> Internal_Company AS company
> ON
> car.comp_id = company.comp_id;
>
> Now you can reference this view in your three-table join instead.
>
> Still, I think you need to take a very elemental course on database
> structure and design... Or do a lot more reading, or something. Again, not
> trying to be offensive, but you are trying to do things that don't make
> sense because a real basic comprehension is missing...
One other question, now the join table view is created. To use the 3
tables for my report I am assuming that I still
need to populate the join table by using the first
query given above with the INSERT INTO .... from the company and
car table INTO The join view table?
thanks,

On Jun 16, 12:28 pm, "Aaron Bertrand [SQL Server MVP]"
<ten....@dnartreb.noraa> wrote:
> > II definitely need the join file. I have a report that shows each car
> > by owner and lessee but it only shows cars by current owners not
> > previous owners. have a question on how to create the view. Do I
> > create the view with only the join table? Then I can use that join
> > table view to select all
> > owners of one car or all lessee's of one owner etc. etc? Or do I
> > create the view with all three tables?
>
> Janis, I don't want to offend you, but I think you need to step back and
> start at a more basic step. It seems you are not even clear on the
> differences between a view and a table?
>
> Here you are trying to create a table that has this data:
>
> SELECT
> car.Car_ID,
> company.Comp_ID
> FROM
> Internal_Car AS car
> INNER JOIN
> Internal_Company AS company
> ON
> car.comp_id = company.comp_id;
>
> Presumably, you will later reference this table in some three-table join.
>
> Why not create a view using this exact query:
>
> CREATE VIEW dbo.View_Company_Car
> AS
> SELECT
> car.Car_ID,
> company.Comp_ID
> FROM
> Internal_Car AS car
> INNER JOIN
> Internal_Company AS company
> ON
> car.comp_id = company.comp_id;
>
> Now you can reference this view in your three-table join instead.
>
> Still, I think you need to take a very elemental course on database
> structure and design... Or do a lot more reading, or something. Again, not
> trying to be offensive, but you are trying to do things that don't make
> sense because a real basic comprehension is missing...

I need to create the SELECT query relationships from `company` to
`view_company_car_join` to car_owner
in order to do the report.
I don't know how to do the second INNER JOIN from the view to
company. Should it be a sub-query?
SELECT INTERNAL_COMPANY.comp_id, INTERNAL_COMPANY.company_name,
view.comp_id

FROM
INTERNAL_COMPANY AS company
View_Company_CAr_join AS view

INNER JOIN
View_Company_CAr_join.comp_ID = company.comp_id

thanks again,
--------------------

Here are my columns and tables:

company:
companyId
company_type [owner, lessee]
address
city
state
zip

VIEW_company_car_join:
carID
companyID

car_owner:
carOwnerID
car_id
start_date
end_date


car:
carID
carMarkI

This should be a simple join between the three tables. See if this query
s:

SELECT B.car_id, A.comp_id, A.company_name, C.carownerid
FROM Internal_Company AS A
JOIN Internal_Car AS B
ON A.comp_id = B.comp_id
JOIN Car_Owner AS C
ON B.car_id = C.car_id;

It is best to post the CREATE TABLE statements with INSERT statements for
sample data for each table and the desired results. That will to get
accurate answer.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

On Jun 16, 7:29 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> This should be a simple join between the three tables. See if this query
> s:
>
> SELECT B.car_id, A.comp_id, A.company_name, C.carownerid
> FROM Internal_Company AS A
> JOIN Internal_Car AS B
> ON A.comp_id = B.comp_id
> JOIN Car_Owner AS C
> ON B.car_id = C.car_id;
>
> It is best to post the CREATE TABLE statements with INSERT statements for
> sample data for each table and the desired results. That will to get
> accurate answer.
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

thanks,

On Jun 16, 7:29 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> This should be a simple join between the three tables. See if this query
> s:
>
> SELECT B.car_id, A.comp_id, A.company_name, C.carownerid
> FROM Internal_Company AS A
> JOIN Internal_Car AS B
> ON A.comp_id = B.comp_id
> JOIN Car_Owner AS C
> ON B.car_id = C.car_id;
>
> It is best to post the CREATE TABLE statements with INSERT statements for
> sample data for each table and the desired results. That will to get
> accurate answer.
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

Hi:
I changed the select a little becuase you left out the join table. I
figure I need the join table otherwise the B.car_id=C.car_id
doesn't work because it is a many to many. So this new one worked but
there is one problem. It gave me
the empty set.
So the car_owner table would give me the cars owned by each company
and if that same car was owned by another company it would show that
as well.
I am back to the beginning because I don't know how to get the
matching company_id's from company (A) into the join view table (B)
and from
car_owner table (C). I need to understand views better however I read
the MySQL 5.0 Views white paper and it was not easy to understand.
I guess I am supposed to do an INSERT data into this join view table
and that is why there are no records in this query?
I don't understand how that update happens. Do I need an update query
that every time a record is added to the A and C tables it updates the
B join view table?


SELECT B.car_id, A.comp_id, A.comp_name
FROM INTERNAL_COMPANY AS A
JOIN View_Company_Car_jn AS B ON A.comp_id = B.Comp_id
JOIN INTERNAL_CAR_OWNER AS C ON B.car_id = C.car_id

------This was my create statement for join table view-------------

CREATE VIEW dbo.View_Company_Car
AS
SELECT
car.Car_ID,
company.Comp_ID
FROM
Internal_Car AS car
INNER JOIN
Internal_Company AS company
ON
car.comp_id = company.comp_id;


thanks,

On Jun 16, 7:29 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> This should be a simple join between the three tables. See if this query
> s:
>
> SELECT B.car_id, A.comp_id, A.company_name, C.carownerid
> FROM Internal_Company AS A
> JOIN Internal_Car AS B
> ON A.comp_id = B.comp_id
> JOIN Car_Owner AS C
> ON B.car_id = C.car_id;
>
> It is best to post the CREATE TABLE statements with INSERT statements for
> sample data for each table and the desired results. That will to get
> accurate answer.
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

On Jun 16, 7:29 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> This should be a simple join between the three tables. See if this query
> s:
>
> SELECT B.car_id, A.comp_id, A.company_name, C.carownerid
> FROM Internal_Company AS A
> JOIN Internal_Car AS B
> ON A.comp_id = B.comp_id
> JOIN Car_Owner AS C
> ON B.car_id = C.car_id;
>
> It is best to post the CREATE TABLE statements with INSERT statements for
> sample data for each table and the desired results. That will to get
> accurate answer.
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

Hi:
I changed the select a little becuase you left out the join table. I
figure I need the join table otherwise the B.car_id=C.car_id
doesn't work because it is a many to many. So this new one worked but
there is one problem. It gave me
the empty set.
So the car_owner table would give me the cars owned by each company
and if that same car was owned by another company it would show that
as well.
I am back to the beginning because I don't know how to get the
matching company_id's from company (A) into the join view table (B)
and from
car_owner table (C). I need to understand views better however I read
the MySQL 5.0 Views white paper and it was not easy to understand.
I guess I am supposed to do an INSERT data into this join view table
and that is why there are no records in this query?
I don't understand how that update happens. Do I need an update query
that every time a record is added to the A and C tables it updates the
B join view table? According to the post from Bertrand I was supposed
to use
a view instead of a table so I didn't have to update anything.

SELECT B.car_id, A.comp_id, A.comp_name
FROM INTERNAL_COMPANY AS A
JOIN View_Company_Car_jn AS B ON A.comp_id = B.Comp_id
JOIN INTERNAL_CAR_OWNER AS C ON B.car_id = C.car_id

------This was my create statement for join table view-------------
CREATE VIEW dbo.View_Company_Car
AS
SELECT
car.Car_ID,
company.Comp_ID
FROM
Internal_Car AS car
INNER JOIN
Internal_Company AS company
ON
car.comp_id = company.comp_id;

thanks, sorry if I don't get it but if I could understand the basics
then I could get it.

I still think you do not need the view. Please post sample data from each
table and what you expect the result will be. Also, explain what columns
define the relations between tables.

In addition you mention MySQL 5.0, and this is SQL Server newsgroup. You may
want to check the MySQL newsgroups as they can provide more specific
information (although you seem to have a rather standard join here).

HTH,

Plamen Ratchev
http://www.SQLStudio.com

On Jun 17, 11:23 am, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> I still think you do not need the view. Please post sample data from each
> table and what you expect the result will be. Also, explain what columns
> define the relations between tables.
>
> In addition you mention MySQL 5.0, and this is SQL Server newsgroup. You may
> want to check the MySQL newsgroups as they can provide more specific
> information (although you seem to have a rather standard join here).
>
You might be right my ERD diagram needs a join table but the database/
tables don't? Or rather you can do join table relationships without
creating a specific join table? Here is some sample data. I changed
some of the data fields to show the possible exceptions since I am
only showing a few records. It is possible for one car to have more
than one owner therefore the car mark can be changed. It is even
possible for the car to get overhauled and get a new serial number.
The master lease is only the account number. A company can have more
than one lease and only one master lease. The car_lease is the line
items for the lease one car_lease per car on each lease.

What I want in this report is a record for each car owner that shows
the lesee of the car. I have that in the following view but I I have
no way of knowing if the car count is correct because it would only
show the first instance of a car owner. The Lessee is type “1” of
company_type field. That is without the join table. The way it is
now is company relates to car_owner as a one to many which it is not.
It is a many to many.

Many thanks,
Car owner’s report per leased car
Lease_description, lease_commencement_date, lease_term_date,lease_num,
Lessee,rental rate, owner, car_mark, car_number, car_lease_id
Net, 2006-03-26 ,2016-04-30, B1-05 , Lessee name, 454.000000,
20.000000 30.000000 454.000000, owner company, 2006-1, 532, 2
TABLES:
comp_id, company_type, company_name, address1, address2, city, state,
zip,country
413, 1, Company 1, NULL, NULL, NULL, NULL, NULL, NULL;
411, 1, Company 2, NULL, NULL, NULL, NULL, NULL, NULL;
car_owner
car_id, comp_id, start date, end date
532, 413, '2007-12-08', '2007-11-08';
601, 411, '2006-06-16', NULL;
602, 411, '2006-06-16', NULL;
603, 411, '2006-06-16', NULL;
604, 411, '2006-06-16', NULL;
532, 411, ‘2007-01-01’, NULL;
Car
car_id, equip_id, serial_no, build date, etc., car_type,comments
532, NULL, 2525, '2003-07-01', NULL, 11, 286000, 42500, 243500,
0, ;Lading cement car, for lease;
601, NULL, 2526, ‘2003-07-01’, NULL, 11, 28600, 42500, 243500,
0,Aluminum Bethgon II car, for lease;
602, NULL, 2527, '2003-07-01', NULL, 11, 286000, 42500, 243500, 0,
etc.
remark
remark_id,car_id, car_mark, car_number, etc.
1, 605, MCO, 288004, 2006-06-01, NULL;
2, 532, MID, 5201, 2006-03-0, NULL;
3, 605, MID, 5202, 2006-03-01, NULL;
Master_Lease
ML_id, company_id, account_Number
1, 413, EST-01;
2, 411, T1-01;
3, 400, D1-05;
4, 399, 'B1-05';
Leases
Lease_id, master _lease_id, lease_type, rate, billing_id
60, 1, 1, 0.000000, 4;
50, 2, 2, 402.570000, 1;
57, 3, 1, 345.905700;
48, 4, 1, 454.000000, 1;
Car_Lease
Car_lease_id, remark_id, lease_id, start_date, etc.
1, 605, 58, 1, 2006-06-01, NULL;
2, 532, 58, 2, 2006-06-01, NULL;
3, 630, 58, 3, 2006-06-01, NULL;
4,605, 50, 1, 2006-06-16, NULL;
5, 592, 58, 4, 2006-06-01, NULL;

On Jun 17, 11:23 am, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> I still think you do not need the view. Please post sample data from each
> table and what you expect the result will be. Also, explain what columns
> define the relations between tables.
>
[CORRECTION]
Plamen
You might be right my ERD diagram needs a join table but the database/
tables don't? Or rather you can do join table relationships without
creating a specific join table? Here is some sample data. I changed
some of the data fields to show the possible exceptions since I am
only showing a few records. It is possible for one car to have more
than one owner therefore the car mark can be changed. It is even
possible for the car to get overhauled and get a new serial number.
The car is identified on the car_lease by the car remark not by the
car_id. The master lease is only the account number. A company can
have more than one lease and only one master lease. The car_lease is
the line items for the lease one car_lease per car on each lease.

What I want in this report is a record for each car owner that shows
the lesee of the car. I have that in the following view but I I have
no way of knowing if the car count is correct because it would only
show the first instance of a car owner. The Lessee is type “1” of
company_type field. I can give you the SQL for the create view table
if you wish however it is hard to read.
Car owner’s report per leased car
Lease_description, lease_commencement_date, lease_term_date,lease_num,
Lessee,rental rate, owner, car_mark, car_number, car_lease_id
Net, 2006-03-26 ,2016-04-30, B1-05 , Lessee name, 454.000000,
20.000000 30.000000 454.000000, owner company, 2006-1, 532, 2
TABLES:
comp_id, company_type, company_name, address1, address2, city, state,
zip,country
413, 1, 'Company 1', NULL, NULL, NULL, NULL, NULL, NULL);
411, 1, 'Company 2', NULL, NULL, NULL, NULL, NULL, NULL;
car_owner
car_id, comp_id, start date, end date
532, 413, '2007-12-08', '2007-11-08';
601, 411, '2006-06-16', NULL;
602, 411, '2006-06-16', NULL;
603, 411, '2006-06-16', NULL;
604, 411, '2006-06-16', NULL;
532, 411, ‘2007-01-01’, NULL;
Car
car_id, equip_id, serial_no, build date, etc., car_type,comments
532, NULL, 2525, '2003-07-01', NULL, 11, 286000, 42500, 243500, 0,
'Aluminum Bethgon II cars', 'for lease';
601, NULL, 2526, ‘2003-07-01’, NULL, 11, 28600, 42500, 243500,
0,’Aluminum Bethgon II cars’, ‘for lease’;
602, NULL, 2527, '2003-07-01', NULL, 11, 286000, 42500, 243500, 0,
'Aluminum Bethgon II cars', 'for
remark
remark_id,car_id, car_mark, car_number, etc.
1, 605, 'CMO', 288004, '2006-06-01', NULL;
2, 532, 'MMID', 5201, '2006-03-01', NULL;
3, 605, 'MMID', 5202, '2006-03-01', NULL;
Master_Lease
ML_id, company_id, account_Number
1, 413, 'DEST-01';
2, 411, 'T1-01';
3, 400, 'D1-05';
4, 399, 'B1-05';
Leases
Lease_id, master _lease_id, lease_type, rate, billing_id
60, 1, 1, '0.000000', 4;
50, 2, 2, '802.570000', 1;
57, 3, 1, '345.905700';
48, 4, 1, '454.000000', 1;
Car_Lease
Car_lease_id, remark_id, lease_id, start_date, etc.
1, 605, 58, 1, '2006-06-01', NULL;
2, 532, 58, 2, '2006-06-01', NULL;
3, 630, 58, 3, '2006-06-01', NULL;
4,605, 50, 1, '2006-06-16', NULL;
5, 592, 58, 4, '2006-06-01', NUL);
Janis

On Jun 17, 11:23 am, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> I still think you do not need the view. Please post sample data from each
> table and what you expect the result will be. Also, explain what columns
> define the relations between tables.
>
[Another correction]
You might be right my ERD diagram needs a join table but the database/
tables don't? Or rather you can do join table relationships without
creating a specific join table? At least can you tell me if I need a
join table?

Here is some sample data. I changed some of the data fields to show
the possible exceptions since I am only showing a few records. It
is possible for one car to have more than one owner therefore the car
mark can be changed. It is even possible for the car to get
overhauled and get a new serial number. The car is identified on the
car_lease by the car remark not by the car_id. The master lease is
only the account number. A company can have more than one lease and
only one master lease. The car_lease is the line items for the lease
one car_lease per car on each lease.
*****NOTE*** : The lessee has a table which I don’t think is necessary
called “INTERNAL_Customer”. It would appear to me that the company
table with a type of “2” or whatever for lessee would be better than
having a separate table for the customer.

What I want in this report is a record for each car owner that shows
the lesee of the car. I have that in the following view but I I have
no way of knowing if the car count is correct because it would only
show the first instance of a car owner. The Lessee is type “1” of
company_type field. I can give you the SQL for the create view table
if you wish however it is hard to read.
Car owner’s report per leased car
Lease_description, lease_commencement_date, lease_term_date,lease_num,
Lessee,rental rate, owner, car_mark, car_number, car_lease_id
Net, 2006-03-26 ,2016-04-30, B1-05 , Lessee name, 454.000000,
20.000000 30.000000 454.000000, owner company, 2006-1, 532, 2
TABLES:
comp_id, company_type, company_name, address1, address2, city, state,
zip,country
413, 1, 'Company 1', NULL, NULL, NULL, NULL, NULL, NULL);
411, 1, 'Company 2', NULL, NULL, NULL, NULL, NULL, NULL;
car_owner
car_id, comp_id, start date, end date
532, 413, '2007-12-08', '2007-11-08';
601, 411, '2006-06-16', NULL;
602, 411, '2006-06-16', NULL;
603, 411, '2006-06-16', NULL;
604, 411, '2006-06-16', NULL;
532, 411, ‘2007-01-01’, NULL;

INTERNAL_Customer
Comp_id, comp_code,
405, 'TVA';
429, 'TXI';
395, 'J9r';
398, 'FLR';
Car
car_id, equip_id, serial_no, build date, etc., car_type,comments
532, NULL, 2525, '2003-07-01', NULL, 11, 286000, 42500, 243500, 0,
'Aluminum Bethgon II cars', 'for lease';
601, NULL, 2526, ‘2003-07-01’, NULL, 11, 28600, 42500, 243500,
0,’Aluminum Bethgon II cars’, ‘for lease’;
602, NULL, 2527, '2003-07-01', NULL, 11, 286000, 42500, 243500, 0,
'Aluminum Bethgon II cars', 'for
remark
remark_id,car_id, car_mark, car_number, etc.
1, 605, 'CMO', 288004, '2006-06-01', NULL;
2, 532, 'MMID', 5201, '2006-03-01', NULL;
3, 605, 'MMID', 5202, '2006-03-01', NULL;
Master_Lease
ML_id, company_id, account_Number
1, 413, 'DEST-01';
2, 411, 'T1-01';
3, 400, 'D1-05';
4, 399, 'B1-05';
Leases
Lease_id, master _lease_id, lease_type, rate, billing_id
60, 1, 1, '0.000000', 4;
50, 2, 2, '802.570000', 1;
57, 3, 1, '345.905700';
48, 4, 1, '454.000000', 1;
Car_Lease
Car_lease_id, remark_id, lease_id, start_date, etc.
1, 605, 58, 1, '2006-06-01', NULL;
2, 532, 58, 2, '2006-06-01', NULL;
3, 630, 58, 3, '2006-06-01', NULL;
4,605, 50, 1, '2006-06-16', NULL;
5, 592, 58, 4, '2006-06-01', NUL);

Janis

What you posted has a lot of errors, as I see columns not matching values or
missing columns. From what I can make sense of here is a query to try. I am
not even sure the joins are correct as you did not describe the relations
between the different tables.

SELECT A.comp_id,
A.company_name,
B.startdate,
B.enddate,
C.account_number,
D.lease_type,
D.rate,
E.startdate,
E.car_lease_id,
F.car_mark,
F.car_number,
G.serial_no
FROM Internal_Company AS A
JOIN Car_Owner AS B
ON A.comp_id = B.comp_id
JOIN Master_Lease AS C
ON A.comp_id = C.company_id
JOIN Leases AS D
ON D.master_lease_id = C.ml_id
JOIN Car_Lease AS E
ON D.lease_id = E.lease_id
JOIN Remark AS F
ON F.remark_id = E.remark_id
JOIN Car AS G
ON G.car_id = B.car_id
WHERE A.company_type = 1;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

On Jun 17, 3:31 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> What you posted has a lot of errors, as I see columns not matching values or
> missing columns. From what I can make sense of here is a query to try. I am
> not even sure the joins are correct as you did not describe the relations
> between the different tables.
>
> SELECT A.comp_id,
> A.company_name,
> B.startdate,
> B.enddate,
> C.account_number,
> D.lease_type,
> D.rate,
> E.startdate,
> E.car_lease_id,
> F.car_mark,
> F.car_number,
> G.serial_no
> FROM Internal_Company AS A
> JOIN Car_Owner AS B
> ON A.comp_id = B.comp_id
> JOIN Master_Lease AS C
> ON A.comp_id = C.company_id
> JOIN Leases AS D
> ON D.master_lease_id = C.ml_id
> JOIN Car_Lease AS E
> ON D.lease_id = E.lease_id
> JOIN Remark AS F
> ON F.remark_id = E.remark_id
> JOIN Car AS G
> ON G.car_id = B.car_id
> WHERE A.company_type = 1;
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

I thought you wanted me to redo my example tables for you? I was
trying .There is a customer_lessee table. Uhh, ohh, I was working on
the ERD all morning and I noticed a prob. That would make the customer
table an unnecessary table. There are only 3 fields comp_id,
comp_code, and investment_grade. Since there is a company type field
of "1" which is lessee then a self join on the company table with the
type field would give all companies of type lessee and I can drop the
customer table. I'm not sure if that is absolutely necessary because
there are php pages for customers involved but I have to develop an
invoice and with unnecessaary tables it seems like having it right
would be best? So far I need all the other table because of
exceptions. The car leases can change, the aar codes can change.
What I really want to know is the join table necessary?
I will see if I can make sense of your query.
Janis

On Jun 17, 3:31 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> What you posted has a lot of errors, as I see columns not matching values or
> missing columns. From what I can make sense of here is a query to try. I am
> not even sure the joins are correct as you did not describe the relations
> between the different tables.
>
> SELECT A.comp_id,
> A.company_name,
> B.startdate,
> B.enddate,
> C.account_number,
> D.lease_type,
> D.rate,
> E.startdate,
> E.car_lease_id,
> F.car_mark,
> F.car_number,
> G.serial_no
> FROM Internal_Company AS A
> JOIN Car_Owner AS B
> ON A.comp_id = B.comp_id
> JOIN Master_Lease AS C
> ON A.comp_id = C.company_id
> JOIN Leases AS D
> ON D.master_lease_id = C.ml_id
> JOIN Car_Lease AS E
> ON D.lease_id = E.lease_id
> JOIN Remark AS F
> ON F.remark_id = E.remark_id
> JOIN Car AS G
> ON G.car_id = B.car_id
> WHERE A.company_type = 1;
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

I looked at your query, you are very good at adding all the fields
together. The only problem I see is how can you account for the cars
that have been owned by 2 different owners? Although I think you
almost have it :-)

I am not sure I understand the problem. Doesn't the join to table Car_Owner
give you all car owners?

HTH,

Plamen Ratchev
http://www.SQLStudio.com

On Jun 18, 1:59 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> I am not sure I understand the problem. Doesn't the join to table Car_Owner
> give you all car owners?
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com
This query doesn't reference the join table does it? It would need to
reference the join table.
So do I need to add the join query above with this new query to get
all the fields on the report?
Actually with the Customer table removed I think I need a self-join on
company & type =1
and a self join on company type = 3
I will see if I can do it and get back to you.

On Jun 18, 1:59 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> I am not sure I understand the problem. Doesn't the join to table Car_Owner
> give you all car owners?
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

I do not think so because that would be a one to many relationship but
I don't know that is why I'm asking .
This is a many to many relationship.

For example
Company A owns Car 1, Car 2
Company B owns Car 3, Car 4
Company C also owns Car 1, Car 4

Now if you do your join company.comp_id = car.comp_id
What will the result be?
Won't it be?
A, 1
A,2
B 3
B 4
Or will it be
A,1
A,2
B,3
B,4
C, 1
C,4
Wouldn't that mean there is no difference between a 1 to many and a
many to many relationship?
by the way, Thanks for your query, I think I can make it work.
Janis