What type of Join is used?

Question

I have a main table and 3 tables that extend the "core" table in different ways.

I work with MLS data, and I have a “common” table that contains information common to all mls lists, and then a table that has specially “residential” information, one for “commercial”, etc .... using the mls number to join the same table when I know the list, when the type of the property is known, but for the search I want to join all of them and have special fields for the search criteria (and not just search for a common table).

What type of connection will give me a dataset that will contain all lists (including extended fields in idx tables)?

For each record in the shared table, there is one corresponding record in ONLY ONE for idx tables.

___________ | | | COMMON | | | |___________| _|_ | ___________________|_____________________ _|_ _|_ _|_ _____|_____ _____|______ ____|______ | | | | | | | IDX1 | | IDX2 | | IDX3 | | | | | | | |___________| |____________| |___________| 

Answer 1

If you want everything in one line, you can use something like this format. This basically gives you all the “common” fields, and then the other fields, if there is otherwise a NULL match:

 SELECT Common.*, Idx1.*, Idx2.*, Idx3.* FROM Common LEFT JOIN Idx1 ON Idx1.MLSKey = Common.MLSKey LEFT JOIN Idx2 ON Idx2.MLSKey = Common.MLSKey LEFT JOIN Idx3 ON Idx3.MLSKey = Common.MLSKey 

Remember that it is better to list fields than to use SELECT * when possible ...

I also assume that the MySQL syntax is the same as SQL Server, and that is what I use.

Answer 2

I have a similar table setup where the job table is the main table.

I have this query that selects specific elements from each of two other tables:

 SELECT jobs.frequency, twitterdetails.accountname, feeds.feed FROM jobs JOIN twitterdetails ON twitterdetails.ID = jobs.accountID JOIN feeds ON jobs.FeedID = feeds.FeedID WHERE jobs.username ='".$currentuser."';"); 

So, as you can see, there is no specific JOIN, but binding fields are defined. You might just need an extra JOIN line for your setup.

Answer 3

An ugly solution / unsuccessful attempt / perhaps misunderstood the question:

 SELECT common.*,IDX1.field,NULL,NULL FROM COMMON LEFT JOIN IDX1 ON COMMON.ID = IDX1.ID WHERE TYPE="RESIDENTIAL" UNION ALL SELECT common.*,NULL,IDX2.field,NULL FROM COMMON LEFT JOIN IDX2 ON COMMON.ID = IDX2.ID WHERE TYPE="RESIDENTIAL" UNION ALL SELECT common.*,NULL,NULL,IDX3.field FROM COMMON LEFT JOIN IDX3 ON COMMON.ID = IDX3.ID WHERE TYPE="INDUSTRIAL" 

Answer 4

The orbit is close. Use an inner join, not a left join. You do not want the general view to appear in the connection if it does not have a string in idx.

You must combine 3 queries to get the correct results, assuming that each shared record can contain only 1 idx table. Connect "NULL" to populate the columns that are not in each idx table so that they can be merged.

By the way, your table design is good.