Joining Multiple One-To-Many Tables

[mvaughn]

I have 3 MYSQL tables and I am using join to get common records from those 2 tables. I have used the following query but my problem is I am getting the records doubled. The query is as follows in easy format:

SELECT a.id, b.*, c.*
FROM tableA a 
LEFT JOIN tableB b
   ON a.id = b.id
LEFT JOIN tableC c
   ON a.id = c.id
WHERE a.id = 12345

I'm sure that I'm overlooking something simple but I just can't see it.
I'm thinking that the problem lies with the WHERE clause repeating with each result found.

Any ideas?

[forskare]

Re: Joining Multiple One-To-Many Tables

Michael,

Have you tried the Join or other operations found in the operations tab of the Control Panel?

[mvaughn]

Re: Joining Multiple One-To-Many Tables

Hi Ken,
The Operations tab is helpful when using dbf. Unfortunately I am using MySql. I have edited my post to reflect that.

Table A = 1,2,3,4,5,...
Table B = 8,5,1,6,1,1,9,...
Table C = 7,1,8,3,1,1,1,...

If I query on Table A [1] I would expect to get 7 returns (count the 1's)
Instead, I get 14 or more
I've googled and googled to no avail.
This is a very common problem that all developers meet at least once so there has to be a correct way to query 2 or more one-to-many tables.

[[email protected]]

Re: Joining Multiple One-To-Many Tables

I have 3 MYSQL tables and I am using join to get common records from those 2 tables. I have used the following query but my problem is I am getting the records doubled. The query is as follows in easy format:

SELECT a.id, b.*, c.*
FROM tableA a 
LEFT JOIN tableB b
   ON a.id = b.id
LEFT JOIN tableC c
   ON a.id = c.id,
WHERE a.id = 12345

I'm sure that I'm overlooking something simple but I just can't see it.
I'm thinking that the problem lies with the WHERE clause repeating with each result found.

Any ideas?

Maby select distinct a. Etc

[mvaughn]

Re: Joining Multiple One-To-Many Tables

Hi Peter,
Thank you but I have tried Distinct, Order By and Group By and I still get repeats.
Try this yourself. Just substitute TableX with one-to-many tables.

[agillbb]

Re: Joining Multiple One-To-Many Tables

From your simplied example data, you would typically use a union query to gather all possible IDs.
http://www.w3schools.com/sql/sql_union.asp

But you may have over-simplified your example.

Why don't you elaborate a bit more with a real-world example - like customers, orders, order items, etc.... so it is clear what you are trying to achieve.

[mvaughn]

Re: Joining Multiple One-To-Many Tables

Hi Andy, good to hear from you again.
Here you go:

SELECT Distinct a.APPLICANTID, b.*, c.* 
FROM applicants a LEFT JOIN criminalbackground b ON a.APPLICANTID = b.APPLICANTID LEFT JOIN workhistory c ON a.APPLICANTID = c.APPLICANTID WHERE a.APPLICANTID = 12662

This is turning out to be a real brain teaser, lol

EDIT: I considered UNION but the problem is that the one-to-many tables are not equal in column number per W3Schools:
"Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order."

I fail on all of those.

[WokingJon]

Re: Joining Multiple One-To-Many Tables

Michael, I'm not an SQL bunny, but have used INNER JOIN to build a single row from multiple tables. Sample is a bit long (9 joins) but TBH I couldn't be bothered to edit it!

SELECT u1.id, u1.user_login AS C_ID2, u1.user_email AS email, m1.meta_value AS firstname, m2.meta_value AS lastname, m3.meta_value AS title, m4.meta_value AS house, m5.meta_value AS street, m6.meta_value AS pcode, m7.meta_value AS phone, m8.meta_value AS mobile, m9.meta_value AS notes 
FROM (wp_yyc5vh_users u1
	 INNER JOIN wp_yyc5vh_usermeta m1
		 ON  m1.user_id = u1.id  AND  m1.meta_key = 'first_name' 
	 INNER JOIN wp_yyc5vh_usermeta m2
		 ON  m2.user_id = u1.id  AND  m2.meta_key = 'last_name' 
	 INNER JOIN wp_yyc5vh_usermeta m3
		 ON  m3.user_id = u1.id  AND  m3.meta_key = 'pie_text_4' 
	 INNER JOIN wp_yyc5vh_usermeta m4
		 ON  m4.user_id = u1.id  AND  m4.meta_key = 'pie_text_5' 
	 INNER JOIN wp_yyc5vh_usermeta m5
		 ON  m5.user_id = u1.id  AND  m5.meta_key = 'pie_text_8' 
	 INNER JOIN wp_yyc5vh_usermeta m6
		 ON  m6.user_id = u1.id  AND  m6.meta_key = 'pie_text_9' 
	 INNER JOIN wp_yyc5vh_usermeta m7
		 ON  m7.user_id = u1.id  AND  m7.meta_key = 'pie_text_11' 
	 INNER JOIN wp_yyc5vh_usermeta m8
		 ON  m8.user_id = u1.id  AND  m8.meta_key = 'pie_text_12' 
	 INNER JOIN wp_yyc5vh_usermeta m9
		 ON  m9.user_id = u1.id  AND  m9.meta_key = 'pie_textarea_13' )
	 WHERE u1.user_url = 1

[mvaughn]

Re: Joining Multiple One-To-Many Tables

Essentially what I have is an applicant table which is unique,
criminalbackground table which is a non-unique data dump,
workhistory table which is also a non-unique dump.

For a given applicant.applicantID find and return all records in criminalbackground and workhistory who's applicantID matches that of applicant.applicantID.

applicants
.....criminalbackground [one-to-many]
.....workhistory [one-to-many]

...and do this without duplicating rows

[[email protected]]

Re: Joining Multiple One-To-Many Tables

I think this will bring you nearer to the solution

SELECT distinct a.relaties_nummer, b.*, c.*
FROM relaties a
LEFT JOIN fakturen b
ON a.relaties_nummer = b.relaties_nummer_deb /*and a.relaties_nummer = 2*/
LEFT JOIN bericht c
ON a.relaties_nummer = c.Relaties_nummer_gebeld and a.Relaties_nummer != 2
WHERE a.relaties_nummer = 2
union
SELECT distinct a.relaties_nummer, b.*, c.*
FROM relaties a
LEFT JOIN fakturen b
ON a.relaties_nummer = b.relaties_nummer_deb and a.relaties_nummer != 2
LEFT JOIN bericht c
ON a.relaties_nummer = c.Relaties_nummer_gebeld
WHERE a.relaties_nummer = 2

[agillbb]

Re: Joining Multiple One-To-Many Tables

Hey,
If you are populating a grid with these records, then you need to display info in common columns anyways. Go with something like this:

SELECT a.APPLICANTID as AppID, b.myfield1 as field1, b.myfield2 as field2
  FROM applicants a 
  LEFT JOIN criminalbackground b ON a.APPLICANTID = b.APPLICANTID 
UNION
SELECT a.APPLICANTID as AppID, c.myfield23431 as field1, c.myfield74542 as field2
  FROM applicants a 
  LEFT JOIN workhistory c ON a.APPLICANTID = c.APPLICANTID

Note the field names are identical, which is a requirement for union queries...

[mvaughn]

Re: Joining Multiple One-To-Many Tables

Andy, you had a great idea, but instead of returning 5 results I got 1 lol.
As a test I just used this...

SELECT a.APPLICANTID as AppID, b.APPLICANTID as field1
  FROM applicants a 
  LEFT JOIN criminalbackground b ON a.APPLICANTID = b.APPLICANTID
	WHERE a.APPLICANTID = 12662
UNION
SELECT a.APPLICANTID as AppID, c.APPLICANTID as field1
  FROM applicants a 
  LEFT JOIN workhistory c ON a.APPLICANTID = c.APPLICANTID
	WHERE a.APPLICANTID = 12662

This is like too weird.
I'm thinking that I might need a subquery to handle this.
Stay tuned...

[[email protected]]

Re: Joining Multiple One-To-Many Tables

Michael,

I think you didn't look at my reply earlier seriously,
it is what you are looking for

Reply

SELECT distinct a.id, b.*, c.*
FROM relaties a
LEFT JOIN fakturen b
ON a.id = b.id
LEFT JOIN bericht c
ON a.id = c.id and a.id != 2
WHERE a.id = 2
union
SELECT distinct a.id, b.*, c.*
FROM relaties a
LEFT JOIN fakturen b
ON a.id = b.id and a.id != 2
LEFT JOIN bericht c
ON a.id = c.id
WHERE a.id = 2


The first part:
SELECT distinct a.id, b.*, c.*
FROM relaties a
LEFT JOIN fakturen b
ON a.id = b.id
LEFT JOIN bericht c
ON a.id = c.id and a.id != 2
WHERE a.id = 2

Because of the LEFT JOIN bericht c
ON a.id = c.id and a.id != 2

The effect is that you only get the records from A left joined with B, The C is only there to get the fields.

The second part:
SELECT distinct a.id, b.*, c.*
FROM relaties a
LEFT JOIN fakturen b
ON a.id = b.id and a.id != 2
LEFT JOIN bericht c
ON a.id = c.id

Because of the LEFT JOIN fakturen b
ON a.id = b.id and a.id != 2
You only get A left joined with C. A is only there for the fields.

In my opinion just what you are looking for.
The crucial part is the Exclusion of one of the two results by making a "clever" on clause.

[mvaughn]

Re: Joining Multiple One-To-Many Tables

Thank you Pieter for your help, sorry took so long to get back.
I love your join logic as it works simply and reliably, but I settled on wrapping
multiple statements within a Transaction such as:

-- start a new transaction
start transaction;
 
-- get latest order number
select @orderNumber := max(orderNUmber)
from orders;
-- set new order number
set @orderNumber = @orderNumber  + 1;
 
-- insert a new order for customer 145
insert into orders(orderNumber,
                   orderDate,
                   requiredDate,
                   shippedDate,
                   status,
                   customerNumber)
values(@orderNumber,
       now(),
       date_add(now(), INTERVAL 5 DAY),
       date_add(now(), INTERVAL 2 DAY),
       'In Process',
        145);
-- insert 2 order line items
insert into orderdetails(orderNumber,
                         productCode,
                         quantityOrdered,
                         priceEach,
                         orderLineNumber)
values(@orderNumber,'S18_1749', 30, '136', 1),
      (@orderNumber,'S18_2248', 50, '55.09', 2);
-- commit changes    
commit;      
 
-- get the new inserted order
select * from orders a
inner join orderdetails b on a.ordernumber = b.ordernumber
where a.ordernumber = @ordernumber;

Thank you all for a good exercise.
I learned something new here.

[mvaughn]

Re: Joining Multiple One-To-Many Tables

Getting back to the top post, here is how the solution would look for the example given...

START TRANSACTION;
SELECT a.id FROM tableA a WHERE a.id = 12662;
SELECT * FROM tableB b WHERE b.id = 12662;
SELECT * FROM tableC c WHERE c.id = 12662;
COMMIT;

Note that semicolons must be present in your code as this example illustrates:

dim id as n = 12345
dim sqlStatement as c = <<%txt%
START TRANSACTION;
SELECT a.id FROM tableA a WHERE a.id = {id};
SELECT * FROM tableB b WHERE b.id = {id};
SELECT * FROM tableC c WHERE c.id = {id};
COMMIT;
%txt%
sqlStatement = evaluate_string(sqlStatement)

Hope that helps!