Complex SQL Query assistance needed for Delphi.

error77
error77 used Ask the Experts™
on
Hi all,

I need assistance with getting an SQL query together.

I'll try to explain the best I can.

I have a Main Table, A Table 1, a Table 2 and a Table 3

I need a query that does more or less this:

SELECT ALL FROM MAIN TABLE + Select ALL From Table 1 + Select all from Table 2 (Twice) + Select All from Table 3 (Twice)

How can I put this together please?

Thanks

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Do all tables have the same structure???
Ferruccio AccalaiSenior developer, analyst and customer assistance

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

Author

Commented:
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
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Senior developer, analyst and customer assistance
Commented:
Yes, exactly as they are.

It means:
Select all from a where a is maintable,
select all from b where b is Table1,
select all from c1 where c1 is Table2
select all from c2 where c2 is Table2
select all from d1 where d1 is Table3
select all from d2 where d2 is Table3

Your result will be a unic table with any field from any table any field will be called a.field,b.field, c1.field, c2.field etc.

You could also replace a, b, c, c1,c2,d1,d2 with MainTable,Table1,Table2_1,Table2_2,Table3_1,Table3_2 to understand better the fields



Emmanuel PASQUIERFreelance Project Manager
Top Expert 2010

Commented:
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...)
Ferruccio AccalaiSenior developer, analyst and customer assistance

Commented:
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 ;)
Emmanuel PASQUIERFreelance Project Manager
Top Expert 2010

Commented:
 I guess that the asker has at least the main background 

Open 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 ;)

Open in new window

That is an excellent excuse !

Commented:
I must subscribe to epasquier's view.

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

Commented:

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




I, too, must agree with epasquier's assessment.  The query is sufficiently simple that it would, at least to me (and I think to epasquier and Thommy ;-) that the author has very limited knowledge of SQL.  Therefore, an explanation should accompany any SQL provided. ;-)

ewangoya brings up excellent points.

If the tables all have the same columns and/or there is not a means of or desire to try to put everything associated with a given identifiable set on one row, then the UNION ALL approach will create a long list of all rows from each table and can, in fact, include the rows from a given table more than once (see the following).
SELECT *
FROM Table1 T1
UNION ALL
SELECT *
FROM Table2 T2a
UNION ALL
SELECT *
FROM Table2 T2b
UNION ALL
SELECT *
FROM Table3 T3a
UNION ALL
SELECT *
FROM Table3 T3b

Open in new window


NOTE TO AUTHOR:
If the tables all have the same columns with the same datatypes but they are in different orders, then you will need to specify the columns in each SELECT statement and specify them in the same order.  (That's a requirement of the UNION operator. ;-)  If the columns have different datatypes, you will need to CAST or CONVERT them to one, common datatype.

ADDITIONAL NOTE TO AUTHOR:
If the tables do not have the same number of columns, you can either add dummy columns to the tables, as required, so that they wind up with the same numbers and types of data columns or you cannot use the UNION ALL approach.

FINAL NOTE TO AUTHOR:
If you fail to include the ALL of the UNION ALL, the query will exclude duplicate rows (meaning you will get, at most, all the rows from each table ONCE ;-).


If you want everything on one massively long row, then you will need to be able to find a way to identify which rows are associated with which rows in the other tables.  If you can do that (and you want one long row), then you would have a query something like the following:
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

Open in new window

Author

Commented:
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

Emmanuel PASQUIERFreelance Project Manager
Top Expert 2010

Commented:
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

Author

Commented:
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
Emmanuel PASQUIERFreelance Project Manager
Top Expert 2010

Commented:
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

Open in new window

Author

Commented:
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
Geert GOracle dba
Top Expert 2009
Commented:
i would learn sql queries first if i were you
access is not part of my knowledgebase, here is a link:

http://office.microsoft.com/en-us/access-help/CH010072899.aspx
and then apply that knowledge using delphi
http://delphi.about.com/od/database/u/delphi_database_programming.htm

homework ?
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

Open 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

Open 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.
Geert GOracle dba
Top Expert 2009

Commented:
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

Open 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.
Emmanuel PASQUIERFreelance Project Manager
Top Expert 2010

Commented:
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.
Geert GOracle dba
Top Expert 2009

Commented:
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. ;-)
Geert GOracle dba
Top Expert 2009

Commented:
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 ?
Geert GOracle dba
Top Expert 2009

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

Author

Commented:
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/
Emmanuel PASQUIERFreelance Project Manager
Top Expert 2010
Commented:
ok, simply tell me if the little table I put in the message #35779191 looks like something you want, and I'll do it tomorrow

Author

Commented:
Hi epasquier, yes, that looks great, thanks :)
Geert GOracle dba
Top Expert 2009

Commented:
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 ... :)

Author

Commented:
It's not for selling houses really. It's for a house inventory.
Geert GOracle dba
Top Expert 2009

Commented:
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 ?


Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial