Joining tables[Resolved]

[Simply Me]

HI! I have two tables namely, Author and Titles. I want to display on flexgrid.
I have AuID, AuLName and AUFName for Author table and Title, Subject and ISBN for Titles. I would like to display the AuID, AuLName, AUFName, Title, Subject and ISBN in flexgrid.

sample code is very much appreciated. Thank you in advance.

[Hack]

What field links the two tables? Is there an Id field in each?

[Simply Me]

What field links the two tables? Is there an Id field in each?

Actually i'm planning to change the AuID with ISBN so that i can relate the two tables using ISBN. Any suggestion what should i use as link?

[Hack]

There has to be something in both tables that relate the records in one table to records in another table. In this case, and Author Id would make the most sense. If you had have that kind of relationship between the two tables, then getting the information out would be a simple but, without some type of logical relationship between the two, SQL isn't going to know what you are after.

[PilgrimPete]

I don't think that changing AuID for ISBN is necessarily a good idea. Won't each author have (potentially) many Titles?
Why not add AuID to Titles, and then you can link on that column.
(Or if you want to support multiple authors of one title, then have a 'junction table', comprising simply of AuID and ISBN, then you can JOIN though this table...)

[Simply Me]

Ok. I have AuID now in both tables. Any code snippet pls?

[PilgrimPete]

SELECT AuLName, AUFName, Title, Subject, ISBN
FROM Authors a
INNER JOIN Titles t
 ON a.AuID = t.AuID

[Simply Me]

SELECT AuLName, AUFName, Title, Subject, ISBN
FROM Authors a
INNER JOIN Titles t
 ON a.AuID = t.AuID

That's it? a and t are aliases? will the code above display the fields in the flexgrid?

[Hack]

That bugger PilgramPete beat me to again!

I had (almost precisely) the same code snippet he had, so I won't bother repeating it. To dump it to your MSHFlexgrid (if you are using an MSFlexgrid, swap it for an MSHFlexgrid), create a recordset from the query, then use this

Set MSHFlexgrid1.Recordset = adoRs

Where adoRs is the name of the recordset you created by running PilgramPete's query.

[PilgrimPete]

Yep. I'm afraid that's it. There's nothing to this programming malarkey...
Yes, a and t are aliases. I'm too lazy to be typing table names.
Yes, this should populate a flexgrid - if you bind it to the recordset, or loop through the recordset adding the records.

[Simply Me]

Thanks to both of you. I'll try it now.

[Simply Me]

It's working! Following up question. if I am going to add a record. How will the INSERT statement will look like?

[Hack]

It's working! Following up question. if I am going to add a record. How will the INSERT statement will look like?

Something like this line.

INSERT INTO authors (id, lname, fname) VALUES (xx, 'Hack', 'Mr')

[Simply Me]

Something like this line.

INSERT INTO authors (id, lname, fname) VALUES (xx, 'Hack', 'Mr')

You mean i have to have two insert statement, one for the authors and the other for the titles? or there is a way to make is one insert only?

[Hack]

You mean i have to have two insert statement, one for the authors and the other for the titles?

Yes. One for each.

[Simply Me]

why am i getting syntax error here? the code is generated by Access.

strSQL = "SELECT tblAuthors.AuthorID, tblAuthors.AuthorLName, tblAuthors.AuthorFName, tblBooks.ISBN, tblBooks.Title, tblBooks.Subject, tblBooks.AccessionNum, tblBooks.CallNum, tblBooks.Edition, tblBooks.Copyright, tblBooks.Copies, tblBooks.Available" & _
    "FROM tblBooks INNER JOIN (tblAuthors INNER JOIN tblBookAuthor ON tblAuthors.AuthorID = tblBookAuthor.AuthorID) ON tblBooks.ISBN = tblBookAuthor.ISBN"

[szlamany]

Because you are missing a space.

In front of the FROM clause.

strSQL = "SELECT tblAuthors.AuthorID, tblAuthors.AuthorLName, tblAuthors.AuthorFName, tblBooks.ISBN, tblBooks.Title, tblBooks.Subject, tblBooks.AccessionNum, tblBooks.CallNum, tblBooks.Edition, tblBooks.Copyright, tblBooks.Copies, tblBooks.Available" & _
    " FROM tblBooks INNER JOIN (tblAuthors INNER JOIN tblBookAuthor ON tblAuthors.AuthorID = tblBookAuthor.AuthorID) ON tblBooks.ISBN = tblBookAuthor.ISBN"

[Simply Me]

[QUOTE=szlamany]Because you are missing a space.

In front of the FROM clause.

oh my... just that space and i've been trying to figure it out for about 20 mins. now.... anyway thanks!