SQL-JOIN错误=>多个表中的相同列名

I have some trouble trying to join a couple of tables for a time-booking system.

The database look like this:

Database tables:

tbl_events

- int_eventID (INT)
- int_serviceID (INT)
- date_eventDueDate (DATETIME)
- date_eventCreationDate (DATETIME)
- int_userID (INT)
- int_customerID (INT)
- int_eventOnlineBooked (INT)

tbl_customers

- int_customerID (INT)
>>> - int_userID (INT) <= this one gives me headache << 
- str_customerFirstName (VARCHAR)
- str_customerLastName (VARCHAR)
- str_customerEmail (VARCHAR)
- str_customerPassword (VARCHAR)
- str_customerCellPhone (VARCHAR)
- str_customerHomePhone (VARCHAR)
- str_customerAddress (VARCHAR)

tbl_services

int_serviceID (INT)
str_serviceName (VARCHAR)
str_serviceDescription (VARCHAR)
int_servicePrice (INT)
int_serviceTimescale (TIME)

tbl_users

int_userID   (INT)
str_userFirstName (VARCHAR)
str_userLastName (VARCHAR)
str_userEmail (VARCHAR)
str_userPassword (VARCHAR)
str_userCellPhone (VARCHAR)

I've got everything to work as expected by the SQL-query (see below). It gives me all events for a specific "user" during a specific week.

SQL query:

SELECT  int_customerID as customerID,
    int_serviceID as serviceID,
    int_eventID as eventID,
    date_eventDueDate as eventDueDate,
    date_eventCreationDate as eventCreationDate,
    int_eventOnlineBooked as eventOnlineBooked,
    str_serviceName as serviceName,
    int_serviceTimescale as serviceTimescale,
    str_customerFirstName as customerFirstName,
    str_customerLastName as customerLastName,
    str_customerCellPhone as customerCellPhone,
    str_customerHomePhone as customerHomePhone
FROM tbl_events
JOIN tbl_services USING (int_serviceID)
JOIN tbl_customers USING (int_customerID)
WHERE
int_userID = 1 AND
YEARWEEK(date_eventDueDate,1) = 201219

The problem is, I didn't had a column in the tbl_customers table that specified which user that customer belongs to. When I added "int_userID" to tbl_customers the SQL-stopped working and gave me the error message:

<b>Warning</b>:  mysql_num_rows() expects parameter 1 to be resource, boolean given in     <b>/Library/WebServer/Documents/calendar/api/calender_getWeekGetEvents.php</b> on line <b>46</b><br />

Line 46:

if(mysql_num_rows($result)) {
    while($event = mysql_fetch_assoc($result)) {
        $events[] = $event;
    }
}

Any ideas? :)

Thanks / L

douxie4583
douxie4583 可能重复的警告:mysql_fetch_*期望参数1是资源,布尔给定错误
大约 8 年之前 回复

2个回答



如果列名发生冲突,请尝试指定您尝试使用哪个表。 </ p>

  ... 
WHERE
tbl_Events.int_userID = 1 AND
...
</ code> </ pre>
</ div>

展开原文

原文

If column names are colliding try to specify which table you try to use that from.

...
WHERE
tbl_Events.int_userID = 1 AND
...

dongxun7962
dongxun7962 然后接受答案。
大约 8 年之前 回复
duan0531
duan0531 工作得很完美,非常感谢你!
大约 8 年之前 回复



在JOIN查询中为表名使用别名是一种更好的做法,其中在多个表中使用相同的字段名称。</ p>

</ div>

展开原文

原文

It is a better practice to use aliases for table name in JOIN query where same field name is used in multiple tables.

doukang1962
doukang1962 w3schools.com/sql/sql_alias.asp
大约 8 年之前 回复
douyi8315
douyi8315 请在postgresql.org/docs/8.3/static/queries-table-expressions.html上查看“7.2.1.2。表和列别名”主题
大约 8 年之前 回复
duannima8347
duannima8347 当然,我希望我的查询尽可能好。 你能解释一下你的意思吗?
大约 8 年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐