14 Replies - 1062 Views - Last Post: 14 August 2012 - 04:20 AM

#1 xxrhy  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 51
  • Joined: 08-April 12

Query in Joining 4 tables

Posted 13 August 2012 - 09:12 AM

this is my code

SELECT db.roomno,dbt.timee,dbs.subject,dbs.subjectss,dbt.lecturer,dbd.dayy from create_db db,create_dbt dbt,create_dbs dbs,create_dbl dbl,create_dbd dbd where db.ID=dbd.ID AND dbd.ID=dbt.ID AND dbt.ID=dbs.ID AND dbs.ID=dbl.ID


This post has been edited by xxrhy: 13 August 2012 - 09:12 AM

Is This A Good Question/Topic? 0
  • +

Replies To: Query in Joining 4 tables

#2 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 448
  • View blog
  • Posts: 3,103
  • Joined: 12-January 10

Re: Query in Joining 4 tables

Posted 13 August 2012 - 09:14 AM

so what is the question
Was This Post Helpful? 0
  • +
  • -

#3 xxrhy  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 51
  • Joined: 08-April 12

Re: Query in Joining 4 tables

Posted 13 August 2012 - 09:16 AM

it said that no value given for one or more parameters.
i already put values on each database
Was This Post Helpful? 0
  • +
  • -

#4 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 448
  • View blog
  • Posts: 3,103
  • Joined: 12-January 10

Re: Query in Joining 4 tables

Posted 13 August 2012 - 09:25 AM

View Postxxrhy, on 13 August 2012 - 12:12 PM, said:

this is my code

SELECT db.roomno,dbt.timee,dbs.subject,dbs.subjectss,dbt.lecturer,dbd.dayy from create_db db,create_dbt dbt,create_dbs dbs,create_dbl dbl,create_dbd dbd where db.ID=dbd.ID AND dbd.ID=dbt.ID AND dbt.ID=dbs.ID AND dbs.ID=dbl.ID


I dont know how your tables are structured but

SELECT db.roomno,dbt.timee,dbs.subject,dbs.subjectss,dbt.lecturer,dbd.dayy 
from create_db db

inner join create_dbd dbd on dbd.ID=db.ID 
inner join create_dbt dbt on db.ID=dbd.ID 
inner join create_dbs dbs on dbs.ID= dbt.Id 
inner join create_dbl dbl on dbl.ID = dbs.ID



you can try something like that
Was This Post Helpful? 0
  • +
  • -

#5 xxrhy  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 51
  • Joined: 08-April 12

Re: Query in Joining 4 tables

Posted 13 August 2012 - 09:35 AM

i have 4 tables namely

create_db
ID,roomno
create_dbt
ID,timee
create_dbs
ID,subject,subjectss
create_dbl
ID,lecturer
create_dbd
ID,dayy

they all have primakey "ID" i want to join them all to be display in datagrid.

there's a error in the code you provide.
it said
"systanx error there is a missing query operation"

This post has been edited by xxrhy: 13 August 2012 - 09:39 AM

Was This Post Helpful? 0
  • +
  • -

#6 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 448
  • View blog
  • Posts: 3,103
  • Joined: 12-January 10

Re: Query in Joining 4 tables

Posted 13 August 2012 - 09:43 AM

mikssing which operation? it should tell you--- i am gussign it is the where clause---

change this line

inner join create_dbt dbt on db.ID=dbd.ID 


to

inner join create_dbt dbt on dbt.ID=dbd.ID 

Was This Post Helpful? 0
  • +
  • -

#7 xxrhy  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 51
  • Joined: 08-April 12

Re: Query in Joining 4 tables

Posted 13 August 2012 - 09:46 AM

"Syntax Error (missing operator) in query expression 'dbd.ID=db.ID inner join create_dbt dbt on db.ID=dbd.ID inner join create_dbs on dbs.ID=dbt.ID inner join create_dbl dbl on dbl.ID=dbs.I"

thats what exactly the error said
Was This Post Helpful? 0
  • +
  • -

#8 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 448
  • View blog
  • Posts: 3,103
  • Joined: 12-January 10

Re: Query in Joining 4 tables

Posted 13 August 2012 - 09:49 AM

did you try the updated thing i posted
Was This Post Helpful? 0
  • +
  • -

#9 xxrhy  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 51
  • Joined: 08-April 12

Re: Query in Joining 4 tables

Posted 13 August 2012 - 09:51 AM

yes still got the same error
Was This Post Helpful? 0
  • +
  • -

#10 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 448
  • View blog
  • Posts: 3,103
  • Joined: 12-January 10

Re: Query in Joining 4 tables

Posted 13 August 2012 - 10:03 AM

put a where clause at the end

like

where db.id = something you are searching for

Was This Post Helpful? 0
  • +
  • -

#11 xxrhy  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 51
  • Joined: 08-April 12

Re: Query in Joining 4 tables

Posted 13 August 2012 - 10:08 AM

still the same

i try
"SELECT db.roomno,dbt.timee,dbs.subject,dbs.subjectss,dbt.lecturer,dbd.dayy from create_db db inner join create_dbd dbd on dbd.ID=db.ID inner join create_dbt dbt on dbt.ID=dbd.ID inner join create_dbs dbs on dbs.ID= dbt.ID inner join create_dbl dbl on dbl.ID = dbs.ID where db.id = 'roomno'"



and

"SELECT db.roomno,dbt.timee,dbs.subject,dbs.subjectss,dbt.lecturer,dbd.dayy from create_db db inner join create_dbd dbd on dbd.ID=db.ID inner join create_dbt dbt on dbt.ID=dbd.ID inner join create_dbs dbs on dbs.ID= dbt.ID inner join create_dbl dbl on dbl.ID = dbs.ID where db.id = roomno"



and this
"SELECT db.roomno,dbt.timee,dbs.subject,dbs.subjectss,dbt.lecturer,dbd.dayy from create_db db inner join create_dbd dbd on dbd.ID=db.ID inner join create_dbt dbt on dbt.ID=dbd.ID inner join create_dbs dbs on dbs.ID= dbt.ID inner join create_dbl dbl on dbl.ID = dbs.ID where db.id = subject"


Was This Post Helpful? 0
  • +
  • -

#12 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 448
  • View blog
  • Posts: 3,103
  • Joined: 12-January 10

Re: Query in Joining 4 tables

Posted 13 August 2012 - 10:13 AM

id usually isnt a name but a number---if yours is a name ' ' with name between the ' also if this is in a c# or vb.net did you open the sql command?
Was This Post Helpful? 0
  • +
  • -

#13 xxrhy  Icon User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 51
  • Joined: 08-April 12

Re: Query in Joining 4 tables

Posted 13 August 2012 - 10:16 AM

i open it in vb.net
Was This Post Helpful? 0
  • +
  • -

#14 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: Query in Joining 4 tables

Posted 13 August 2012 - 09:06 PM

I notice you';re using reference to db.id as well as db.ID. If you're using VB.NET, then I'm guessing you're using Windows, in which case there could be case sensitivity issues at play here. If it's a MySQL database, try enclosing all the table names and column names in backticks, like this:
SELECT `db`.`roomno`, `dbt`.`timee`...


Also, make sure your table and column names match between the query and the database. Some of those column names look like they could be misspelt, such as subjectss and dayy.
Was This Post Helpful? 0
  • +
  • -

#15 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5805
  • View blog
  • Posts: 12,643
  • Joined: 16-October 07

Re: Query in Joining 4 tables

Posted 14 August 2012 - 04:20 AM

Take these things a step at a time.

Here's one of your queries, laid out legibly.
SELECT
		db.roomno, 
		dbt.timee, 
		dbs.subject, dbs.subjectss,
		dbt.lecturer,
		dbd.dayy 
	from create_db db
		inner join create_dbd dbd 
			on dbd.ID = db.ID 
		inner join create_dbt dbt 
			on dbt.ID = dbd.ID
		inner join create_dbs dbs 
			on dbs.ID = dbt.ID 
		inner join create_dbl dbl 
			on dbl.ID = dbs.ID
	where db.id = 'roomno'



Just for fun, let's strip out the where and loosen the joins. Also, we'll link everyone to db.ID and not do this messy cascading thing.
SELECT
		db.ID,
		db.roomno, 
		dbt.timee, 
		dbs.subject, dbs.subjectss,
		dbt.lecturer,
		dbd.dayy 
	from create_db db
		left outer join create_dbd dbd 
			on dbd.ID = db.ID 
		left outer join create_dbt dbt 
			on dbt.ID = db.ID
		left outer join create_dbs dbs 
			on dbs.ID = db.ID 
		left outer join create_dbl dbl 
			on dbl.ID = db.ID



This will get you every row from create_db, regardless of if they match up with the other tables. You'll see nulls in the fields where the table missed.

Having more meaningful table names could help you out here:
room
	room_id, roomno
room_time
	room_id, timee
room_subject
	room_id, subject, subjectss
room_lecturer
	room_id, lecturer
room_day
	room_id, dayy



This, frankly, looks like a mess. Lets assume you have a subject, taught but an instructor, in a given room, at a given time. Keying everything off room doesn't seem the way to go. Perhaps:
room
	room_id, roomno
subject
	subject_id, subject_name, subjectss
lecturer
	lecturer_id, lecturer_name
room_schedule
	room_id
	timee
	dayy
	subject_id
	lecturer_id



Hope this helps.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1