join from multiple tables

[sagisgirl]

Hi, I'm confused right now about mysql statement

I have four tables :

form1       |   form2   |   form3   |   form4   |
-----------------------------------------------------
k8regNo     |   form2_ID|   k9regNo |   form4_ID    |
consignee   |   goods   |   out_date|   k9regNo     |
moveDt      |   k8regNo |           |   form2_ID    |

can i join this four tables..?

[Rahul47]

You need to specify keys for each table. Like in which are Primary and Foreign keys. Only then one can figure out to join tables.

[sagisgirl]

   form1        | form2        | form3       | form4   
    -----------------------------------------------------
    k8regNo(PK) | form2_ID(PK) | k9regNo(PK) | form4_ID(PK) 
    consignee   | goods        | out_date    | k9regNo 
    moveDt      | k8regNo(FK)  |             | form2_ID(FK)

Is it possible to join these table..?
or is it, the primary key for each tables must be the same, and only then i can join the tables.?

[Rahul47]

1) Like you have field k8regNo in form1, which i suppose is an Integer and some sort of number. So, it can be Primary Key for form1.

2) Similary you have field form2_ID in form2, which i suppose is an Integer too and some sort of ID. So, it can be Primary Key for form2.

3) Now declaring k9regNo as foreign key in form3 which references k9regNo from form1 you can join form 1 and form 3. Similary you can join form1 and form4.

4) Similarly declaring form2_ID as foreign key in form4 which references form2_ID from form2 you can join form 2 and form 4.

[sagisgirl]

Ok.
yes it is correct for statement 1) and 2), but for the statement 3), k9regNo actually as primary key for form3 "k8regNo" and "k9regNo" are different

i want to join these 4 tables but how do I do it.? can you teach me.

[|-|x]

Looks to me as though they join like this:

form1.k8regNo <- form2.k8regNo
form2.form2_ID <- form4.form4_ID
form4.k9regNo <- form3.k9regNo

Does this sound about right?

Do you know how to write joins in SQL?

[sagisgirl]

@Hearth
I just know to join from 2 tables, like this:

"SELECT * FROM form t1, form2 t2 WHERE t1.k8regNo = t2.k8regNo ORDER BY moveDt"

[Rahul47]

This might help:
You need to define k9regNo as FK in form4 referencing k9regNo as PK from form3.

[|-|x]

OK @sagisgirl

Have a read of that article I linked in my last post, its the mysql man page for proper join statements.

As an example, the query you posted could be written like this:

SELECT * 
FROM form1 t1
JOIN form2 t2 ON t1.k8regNo = t2.k8regNo 
-- more joins can be added here...
ORDER BY moveDt

NOTE: the type of join (INNER, LEFT, RIGHT) will affect the result set, so check what they do in the man article to decide which type you need. Or you can try them to see the difference.

[Rahul47]

Let's try if this works:

SELECT * FROM form1 f1, form2 f2
WHERE f1.k8regNo = ( SELECT f2.form2_ID FROM form2 f2, form4 f4
                     WHERE f2.form2_ID= ( SELECT f4.form2_ID FROM form4 f4, form3 f3
                                          WHERE f4.k9regNo=f3.k9regNo));

[sagisgirl]

@Hearth
yes, i did read it..thanks.but im really confused rigt now.

@Rahul47

I tried that, but it gives me an error :

1242 - Subquery returns more than 1 row

what does it mean?

[Rahul47]

It was hardcoded without using any JOINS.
You see when a subquery is executed it will return values which are met against WHERE condition.

Here Subquery is returning more than one values which cannot be handled by WHERE clause in main query.

[Rahul47]

From which fieds do you want to extract data ? Speficy fields from each table.

[Rahul47]

I worked on it and came up with some results which you hopefully might find of help.

[|-|x]

@Rahul47

If a subquery returns more than one row it needs to be compared via IN as = will throw an error.

SELECT * FROM form1 f1, form2 f2
WHERE f1.k8regNo IN ( SELECT f2.form2_ID FROM form2 f2, form4 f4
                 WHERE f2.form2_ID IN ( SELECT f4.form2_ID FROM form4 f4, form3 f3
                                      WHERE f4.k9regNo=f3.k9regNo));

However, subqueries are also an extremely inefficient way of doing this.

Also, re your last post, it is not poor practice not to specify the conditions of a join for readability sake (particularly if she is learning).

@sagisgirl

An updated version of my last example with all tables included:

SELECT * 
FROM form1 t1
JOIN form2 t2 ON t1.k8regNo = t2.k8regNo 
JOIN form4 t4 ON t2.form2_ID = t4.form2_ID
JOIN form3 t3 ON t4.k9regNo = t3.k9regNo 
ORDER BY moveDt

This will return a dataset of all tables joined without filtering of any kind. Without knowing more about the data and the purpose of the query it is impossible for me to make recommendations about the types of joins to use, or other criterea (the resultant dataset may be quite large).

[sagisgirl]

I tried the way @Hearth taught me, i do it like this

SELECT form1.k8regNo, form1.moveDt, form1.consignee, form2.goodsDesc, form2.k8goodsQty, form3.k9regNo, form3.outDt, form4.k9goodsQty
FROM form t1,
LEFT JOIN form2 t2 ON t1.k8regNo = t2.k8regNo
LEFT JOIN form4 t4 ON t2.k8_goodsID = t4.k8goodsID
LEFT JOIN form3 t3 ON t4.k9regNo = t3.k9regNo
WHERE form1.k8regNo = '25'

but it return me an error ,
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left join form2 t2 ON t1.k8regNo = t2.k8regNo LEFT JOIN form4 t4 ON t' at line 1

[|-|x]

You just need to remove the comma from line #2. JOINs don't need to be separated by commas the way a table list is.

[sagisgirl]

ok..I already got it..and it works...thank you guys..