Complex SQL Query assistance needed for Delphi.

Do all tables have the same structure???

Select a.*,b.*,c1.*,c2.*,d1.*,d2.*  from MainTable a, Table1 b, Table2 c1, Table2 c2, Table3 d1, Table3 d2

Can you explain: Select a.*,b.*,c1.*,c2.*,d1.*,d2.*  ... What's the a b and c's?

Do I just enter that in the query?

Thanks

Ferruccio, you might have indulged explaining EXACTLY what that means to do such a selection, has you know, I know, most of us know, that it is going to be a JOINT of all the tables, with all the good and bad it means. It would seem the asker does not know what a JOINT is, and he might be surprised by the result, if it just doesn't kill his application with too much data. I find counseling joining 5 TABLES of which you don't know the structure nor the nb of lines a bit irresponsible.

I'm about 99% sure it is not what the user wants, yet I'm also 99% sure I'm not sure about what he really wants to achieve, probably like Thommy. It might be complete nonsense.

So please error77, explain better what you want, with your tables structures (what fields are in those), and what is the expected result (again in terms of fields, and maybe in terms of relations between the fields, the selections that might be necessary etc...)

Irresponsible? The question seems simple and if any addictional explaination is needed by the author he can always ask, and we all member can already explain as you are actually doing.
if I find a question about SQL in Delphi section I guess that the asker has at least the main background about it, otherwise he should have posted his question in the Database-SQL section.
Anyway you're next to be right, but just next: my answer was a bit too much optimistic, not irresponsible, and yes, I should have ask more explanation about the q., but my wife was calling for lunch ;)

 I guess that the asker has at least the main background 

Select allOpen in new window

Yes, but if he had, then he would not need help with a simple joint. Yet you will agree that this SQL request, while *simple* to write, would very often lead to a huge amount of data, which would possibly hang the application of the asker. In that way, without explanation or warning, that is a bit of a trap. Irresponsible was probably a strong word, I'm sorry for that, but I knew you could handle it with good spirit

besides, I'm afraid that the asker wants a simple concatenation of tables (rows of all the tables one after the other), because it makes more sense than joining 5 tables without explaining the relationship between the tables. If that is the case, then I join Thommy in the need to know the structure, and the 3 puzzled question marks, because it is a bit unusual to ask that to a SGBD. if that is not the case, then it will be next to impossible to have something looking satisfying in a user point of view.

but my wife was calling for lunch ;)

Select allOpen in new window

That is an excellent excuse !

I must subscribe to epasquier's view.

@error77: Please describe more detailed what you want to do...

I'm guessing he wants to view all the records from the tables in one result set, but he just did not explain it well.

First we would need a structure of the tables before anything else.

If the table structures are the same as Thommy inquired then it is very simple

select * from Table1
union all
select * from Table2
union all
select * from Table3

If however, he is looking at relationships, then we cant go any further without knowing the primary and foreign keys of the tables

Hi all,

and thanks for all your replies ...

What I'm trying to do is to following step by step in a real life situation.

I have:

Main Table
- ID
- Name
- Address

Bathroom Table
- ID
-Color
-Floor

Bedroom Table
-ID
-Color
-Floor
-Furniture

Kitchen Table
- ID
-color
-Floor
-Furniture


1. User chooses to create a new entry

2. User selects 2 bathrooms

3. User Selects 2 Bedrooms

4. User Selects 1 Kitchen

5. The SQL Query is create and Run..

6. Output is shown in the DBGrid

I hope this helps to explain what I'm trying to do.

Any ideas on making this better welcome if you want to suggest different.

THanks

Ok, better with the table structures. But I still don't understand what is the result you want.

I suppose by User select this or that you mean there are DB-aware listboxes for all Main, Bathroom etc.. and user can select (0?) one or more of those and then click a button. but to produce what ?
Please describe with words, and/or a sample table, what that result is, and how it is logically made of the selections you have

The tables don't have all the fields yet but here is the DB.

I'm using an AdvStringGrid with can be edited ... unless you have a better idea...

Each table is going to contain about 20 fields so I need to make it as simple to read and scroll as possible.

I'm still not sure which components are the best to use.

The user basically adds a new record and selects a house with 2 bedrooms and a kitchen etc and it's all displayed in a table where the user can then add the data.

Does it all make sense now?

Attached is the DB

  Database1.mdb

something like this ?

then you have to do multiple select with WHERE ID = the ID of the current adress (see parameters in SQL) and use union with all the notes diver explained : order of fields, dummy fields for tables that do not have all (like bedroom)

sorry I don't write one sample of code it i'm typing with 2 fingers having my newborn in other hand :o)

[ Room   ][ Color   ][ Floor   ][ Furniture ]
 Bedroom  | White    | Wood     | - (None)
 Bedroom  | Pink     | Lino     | -
 Kitchen  | Cream    | stone    | whatever

Select allOpen in new window

Congrats on your newborn :o)

This is all going to be controlled by a Delphi App.

So, what would I do in terms of the SQL Query ?

I know you are busy so take your time as I'm not in a rush :)

Thanks again

This falls into the second category that I provided:

SELECT T1.yourcolumn1name T1_yourcolumn1name
  ,T1.yourcolumn2name T1_yourcolumn2name
  ,etc.
FROM Table1 T1
LEFT OUTER JOIN
    (SELECT T2a.yourcolumn1name T2a_yourcolumn1name
      ,T2a.yourcolumn2name T2a_yourcolumn2name
      ,etc.
     FROM Table2 T2a
    ) T2a
ON  T1.commoncolumnname = T2a.commoncolumnname
LEFT OUTER JOIN
    (SELECT T2b.yourcolumn1name T2b_yourcolumn1name
      ,T2b.yourcolumn2name T2b_yourcolumn2name
      ,etc.
     FROM Table2 T2b
    ) T2b
ON  T1.commoncolumnname = T2b.commoncolumnname
LEFT OUTER JOIN
    (SELECT T3a.yourcolumn1name T3a_yourcolumn1name
      ,T3a.yourcolumn2name T3a_yourcolumn2name
      ,etc.
     FROM Table3 T3a
    ) T3a
ON  T1.commoncolumnname = T3a.commoncolumnname
LEFT OUTER JOIN
    (SELECT T3b.yourcolumn1name T3b_yourcolumn1name
      ,T3b.yourcolumn2name T3b_yourcolumn2name
      ,etc.
     FROM Table3 T3b
    ) T3b
ON  T1.commoncolumnname = T3b.commoncolumnname

Select allOpen in new window

However, to fill in the names you provided:

SELECT T1.ID
  ,T1.Name
  ,T1Address
  ,Bath1_Color
  ,Bath1_Floor
  ,Bath2_Color
  ,Bath2_Floor
  ,Bedroom1_Color
  ,Bedroom1_Floor
  ,Bedroom1 Furniture
  ,Bedroom2_Color
  ,Bedroom2_Floor
  ,Bedroom2 Furniture
  ,Kitchen_Color
  ,Kitchen_Floor
  ,Kitchen_Furniture
FROM Main T1
LEFT OUTER JOIN
    (SELECT T2.ID
      ,T2.Color Bath1_Color
      ,T2.Floor Bath1_Floor
    FROM Bathroom T2a
    ) T2a
ON  T1.ID = T2a.ID
LEFT OUTER JOIN
    (SELECT T2b.ID
      ,T2b.Color Bath2_Color
      ,T2b.Floor Bath2_Floor
    FROM Bathroom T2b
    ) T2b
ON  T1.ID = T2b.ID
LEFT OUTER JOIN
    (SELECT T3a.ID
      ,T3a.Color Bedroom1_Color
      ,T3a.Floor Bedroom1_Floor
      ,T3a.Furniture Bedroom1_Furniture
    FROM Bathroom T3a
    ) T3a
ON  T1.ID = T3a.ID
LEFT OUTER JOIN
    (SELECT T3b.ID
      ,T3b.Color Bedroom1_Color
      ,T3b.Floor Bedroom1_Floor
      ,T3b.Furniture Bedroom1 Furniture
    FROM Bathroom T3b
    ) T3b
ON  T1.ID = T3b.ID
LEFT OUTER JOIN
    (SELECT T4.ID T4.color
      ,Kitchen_Color
      ,T4.Floor Kitchen_Floor
      ,T4.Furniture Kitchen_Furniture
    FROM Kitchen T4
    ) T4
ON  T1.ID = T4.ID

Select allOpen in new window

The reason I used a LEFT OUTER JOIN is that the tables may or may not have rows.

NOTE:
You have not provided a means for differentiating the different instance of Baths and Bedrooms (e.g. a column named BedroomNumber).  Also, if the ID column in each table is an Autoincrement or Identity column, then the above will not work because the ID in the Bathroom table will increment totally independently of the ID in the Main table and will have absolutely NO correlation to the rows in the Main table.

Remember, just because the Identity column in the Main table is named ID and the Identity column in the Bathroom/Bedroom/whatever table is also named ID, that doesn't mean that rows from the Main table with a given ID value have anything to do with rows from another table with the same ID value.

why the left outer joins like that ?
it will force the database into loading the whole table before doing the join

it would be far better to join the table
and set the predicates directly on the table
and then alias the columns
 

SELECT T1.ID
  ,T1.Name
  ,T1.Address
  ,T2a.Color as Bath1_Color
  ,T2a.Floor as Bath1_Floor
  ,T2b.Color as Bath2_Color
  ,T2b.Floor as Bath2_Floor 
  -- etc
FROM Main T1
LEFT OUTER JOIN FROM Bathroom T2a ON T1.ID = T2a.ID
LEFT OUTER JOIN FROM Bathroom T2b ON T1.ID = T2b.ID
LEFT OUTER JOIN FROM Bathroom T3a ON T1.ID = T3a.ID  
  -- etc

Select allOpen in new window

haven't you ever had performance problems 8080_Diver ?

My recommendation would be to put the various "rooms" on tabs (or whatever equivalent you choose) so that you can present each "room" in a reasonably readable manner.  This will also let you provide a means for "scrolling" through the rooms of a given type (if you drive that data off of a query on the appropriate table).  

However, you are absolutely going to need to have a column in each of the room tables that identifies which Main table row/record they relate to.  

I also highly recommend that you avoid using ID as the name for Identity columns in tables.  It tends to lead to just the sort of confusion you have indicated with the table layouts you provided, i.e. thinking you can join tables based upon the ID column when you can't.  Instead, qualify the column name, e.g. Main_ID, Bathroom_ID, etc.  Then you will see that you need a Main_ID column in each of the room tables so that you can JOIN them to the Main table in a meaningful manner.

as always, Diver is thorough in his answers :o)
but he did not provided a solution that will produce the kind of result I was talking about : it will put ALL the fields of the tables that concerns ONE address on a SINGLE row. So it is difficult with that to provide a user interface that is readable (long long lines).

If you are giving emphasize on ONE address and can / are willing to display informations in a *normalized* (common fields for all rooms) way, one line per room, then tell us so, and I might be able to code something later tonight

@Geert,
Given the nature of the author's request, the apparent size of the databse, and the indicated level of SQL expertise coupled with the fact that I am responding at the end of my lunch break and, therefore running out of time, I presented a query that will work, that the author should find readable and understandable, and athat I could quickly assemble with some C&P work.

If the author has a billioon rows in each table, hthen the query may need tuning.  On the other hand, if the author has 35 to 100 rows in the tables, it won't matter. ;-)

As for my having performance issues with my queries, I generally slam a query togather in a test (i.e. low data volume) environment and then tune it after it works rather than trying to tune it before and during development.  Each to his own.

you need to write a interface for every table in your db
with functionality to :
  show data in a table > with user selection
  add a item in the table
  remove a item in the table
  change a item in the table

  > if references with other tables
  > the next step is to link those items using the reference

like follows:
create 1 hotel
create 1 room in the hotel
create 1 bathroom in the room
create 1 kitchen in the room

the base object here is the hotel
and everything else is linked to that

epasquier,

it will put ALL the fields of the tables that concerns ONE address on a SINGLE row.
That seemed to be what was being asked for . . . and I did present an alternative approach. ;-)

8080_diver...
that's just it, if you keep in mind all the performance change you did when writing a new query
then you will almost never again have to tune a query

what is the point in doing the same work 2 times ?

and since you don't have much time ...
the query i changed for you has less text

Thanks all for so many answers...only issue is that I'm now super confused with what I'm looking for as there's soo many options :o/

epasquier:

Ref: If you are giving emphasize on ONE address and can / are willing to display informations in a *normalized* (common fields for all rooms) way, one line per room, then tell us so, and I might be able to code something later tonight

 - A sample code would be great as I'm reallly confused now with so many different options :o/

Hi epasquier, yes, that looks great, thanks :)

what you are desribing as a program looks like a search tool for buying/selling a house

people enter the description of a house to sell
other would search based on some criteria, like big kitchen, small garden, etc

if this is the case, then you would write an internet application ... :)

It's not for selling houses really. It's for a house inventory.

so you want a stock / asset inventory program

are you looking for ideas on how to start on such a program ?
or do you have a general blueprint ?

I know what the program needs to do ... it's getting the db part of it together what I'm having issues with.