join 4 tables in query

[nurvirus]

hai,

can we join 4 tables in sql query? if yes, can someone gives reference? thanks!

[Daemonspyre]

You can JOIN as many tables as you have references.

Take, for example, these 4 tables:

Table 1
------------
ID
Field1
Field2

Table 2
------------
ID
t1ID //relates to table1 ID
Field1
Field2

Table 3
------------
ID
t2ID //relates to table2 ID
Field1
Field2

Table 4
------------
ID
t3ID //relates to table3 ID
Field1
Field2




SELECT t1.Field1, t3.Field2 FROM table1 t1 JOIN table2 t2 ON t1.ID = t2.t1ID JOIN table3 ON t2.ID = t3.t2ID JOIN table4 ON t3.ID = t4.t3ID WHERE Conditional = true


DO NOT USE 'SELECT *' as you will return every field on every table. Put in the fields that you want to see in your query.

[nurvirus]

thanks for ur reply. But i dont quite understand the query. here is what i got..

order_ms
order_code
order_customer_code

order_dt
dt_order_order_code
dt_order_product_code

product_ms
product_code

customer_ms
customer_code

[guelphdad]

so try to adapt your tables to the example above and see what you come up with and paste it here. we can help you from that point.

[nurvirus]

here is what ive tried. but i failed to get the results.

product_ms (table 1)
product_code (primary)
product_desc
product_price

order_dt(table 2)
dt_order_order_code (primary)
dt_order_product_code //t1
dt_order_quantity

order_ms(table 3)
order_code(primary) //t2
order_customer_code

customer_ms (table 4)
customer_code (primary) //t3

this is what i understand from the given example:

SELECT dt_order_product_code.product_desc, customer_code.product_price FROM product_ms dt order_product_code JOIN order_dt order_code ON dt_order_product_code.dt_order_order_code JOIN order_ms.order_code= customer_ms.order_ms.order_code WHERE Conditional=true

[guelphdad]

step 1) remove this where clause: WHERE Conditional=true it was meant as an example only

step 2) read the tutorial section in the mysql manual, it is chapter 3

step 3) when you try a join and don't get results or don't get the correct results, or it didn't snow today, you need to elaborate. you are not telling us what is wrong with the query (other than the where clause)

[Daemonspyre]

You are on the right track -- but your SQL is not correct.

1) Follow the statements from Guelphdad above.

2) I do not think you have quite understood my example, and I will show you why.

Your Query:

SELECT dt_order_product_code.product_desc, customer_code.product_price FROM product_ms dt order_product_code JOIN order_dt order_code ON dt_order_product_code.dt_order_order_code JOIN order_ms.order_code= customer_ms.order_ms.order_code WHERE Conditional=true


I was using something called 'Table Aliases' in order to make referencing the tables later on easier. They are basically SQL shortcuts for table names.

In each SQL Query, you have to use the format TABLE_NAME.FIELD_NAME or TABLE_ALIAS.FIELD_NAME.

In your query, you are not using that format, but are using FIELD_NAME.FIELD_NAME. Not going to work.

Take just the first part:
SELECT dt_order_product_code.product_desc, customer_code.product_price FROM product_ms dt order_product_code

Your tables are called product_ms, order_dt, order_ms, and customer_ms. Since you want to pull fields from these tables, they (in this query) need to be listed in the first half of the SQL Query.

Revision:
SELECT order_dt.dt_order_product_code, product_ms.product_desc, customer_ms.customer_code, product_ms.product_price FROM product_ms

See the difference? You were trying to run the JOIN in the FIELD REFERENCE part of the query.

Now, that was a lot of typing. Here's where TABLE ALIASES come into play.

SELECT od.dt_order_product_code, p.product_desc, c.customer_code, p.product_price FROM product_ms p

Note: I am still using only the first part of the query, so you won't see where table aliases for your other 3 tables are built YET.

With me so far?

Second part of the query:

FROM product_ms dt order_product_code JOIN order_dt order_code ON dt_order_product_code.dt_order_order_code JOIN order_ms.order_code= customer_ms.order_ms.order_code WHERE Conditional=true

Well, as Guelphdad said, I was only using the 'WHERE Conditional=true' as an example of a WHERE statement. Don't need it throw it out...

Again, you are using the wrong TABLE_NAME.FIELD_NAME syntax. Also, your JOIN is not looking at the right fields.

JOIN means to reference table1 to table2, where a field in table2 is the same as a field in table1.

You are trying to join on the wrong fields.

Revision:

FROM product_ms p JOIN order_dt od ON p.product_code = od.dt_order_product_code


Now, I cannot continue because a) you need to learn how to do this and b) there is a slight error in your table definitions.

How are order_dt and order_ms related? They are not, at least not in this table structure.

Try changing
order_ms
order_code(primary)
order_customer_code

to

order_ms
order_code(primary)
dt_order_order_code // this field will be the relation, as well as the JOIN.
order_customer_code


Of course, this means that you will need to adjust some data as well.

Hope this helps, and please let us know, with a little more information as to any error messages, if you need any more help or if you have any questions.