SELECT query for multiple column with multiple tab

问题:

I attach one image of my problem:

1. In Testing jFrame jTextField I will insert customer id then after pressing ok button query will select and collect information related to that customer.Then it will show in the jTableModel.
2. I attach my database image.
3. Error is "SQL code cannot be executed".

回答1:

You can do like this (without Join):

SELECT   papers.paper_list,papers_rate.monday,papers_rate.tuesday,
papers_rate.wednesday,papers_rate.thrsday,papers_rate.friday,
papers_rate.saturday,papers_rate.sunday,magzines.magzine_name,magzines_rate.rate 
FROM papers,papers_rate,magzines,magzines_rate 
WHERE example_table.customer_id = ? AND other conditions"

This syntax is, in effect, a simple INNER JOIN. Some databases treat it exactly the same as an explicit JOIN. The WHERE clause tells the database which fields to correlate, and it returns results as if the tables listed were combined into a single table based on the provided conditions.(http://www.techrepublic.com/article/sql-basics-query-multiple-tables/)

回答2:

You need to join the tables properly.
Like this:

SELECT
   paper_list,monday,tuesday,wednesday,thrsday,friday,saturday,sunday,magzine_name,rate 
FROM papers

LEFT JOIN papers_rate
    ON papers_rate.paperId = papers.id

LEFT JOIN magzines
     ON magzines.paperId = papers.id

LEFT JOIN magzines_rate 
     ON magzines_rate.magazineId = magzines.id

WHERE customer_id = ?"

If you do an inner join, all your results will vanish if you don't have a magazine_rate for example...

And check your spelling.
You're writing thrsday instead of thursday and magzine instead of magazine...

PS: And where does customer_id come from ?

回答3:

Use joins to select multiple column from multiple tables. Refer this to get an understanding about the join and for join examples.

Note: There should be a common field between two tables to perform join operation

回答4:

If the tables are related you must use JOIN: let's see an example (I don't know your tables fields, so I'll invent a custom example). Think about person and pet tables; the person tables could contain these fields:

Person (personID, first_name, last_name, age)

the pet table could contain these other fields:

Pet (petID, name, age, personID)

The personID field in the pet table identifies the owner of the pet. It is a simple 1:N relation. To select some values from these two tables you must do something like:

SELECT Person.first_name, Person.last_name, Pet.name
FROM Person INNER JOIN Pet ON
Person.personID = Pet.personID
WHERE Person.age > 30

This is just an example, clearly. And the INNER JOIN is just a join type (there are several join methods). Here you can find some documentation concerning these issues.

回答5:

You need to either use a Join clause (... FROM papers JOIN papers_rate ON papers.[id_column] = papers_rate.[foreign_key]) or use an equi-join (replace JOIN...ON clause with a condition in the WHERE clause) (... FROM papers,papers_rate WHERE papers.[id] == papers_rate.[foreign_key])

Could you please post the schema of your tables?