Query Join please help

[vsailas]

Hi all,
I am a novice at SQL programming, I am in a fix with a new requirement…

I want a query that will fetch me username( from users table),
Ip_start and ip_end (from ip_range table) and accesstype (from subscription table) for corresponding user_no.

Like this
[user 1]
Username, 192.168.1.1. 192.168.1.1, read-only

[user 2]
example_user, 0.0.0.0, 0.0.0.9, read-only

Please help me!!!!

following are the table description.


users
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| users_no | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(255) | NO | | | |
| password | varchar(255) | NO | | | |
| email | varchar(255) | YES | | NULL | |
| firstname | varchar(255) | YES | | NULL | |
| middleinitial | varchar(10) | YES | | NULL | |
| lastname | varchar(255) | YES | | NULL | |
| companytype | varchar(255) | YES | | NULL | |
| companyname | varchar(255) | YES | | NULL | |
| department | varchar(255) | YES | | NULL | |
| role | varchar(255) | YES | | NULL | |
| title | varchar(10) | YES | | NULL | |
| mailinglist | varchar(255) | YES | | NULL | |
| question | varchar(255) | YES | | NULL | |
| answer | varchar(255) | YES | | NULL | |
| last_login | date | YES | | NULL | |
| last_login_ip | varchar(20) | YES | | NULL | |
| isadmin | varchar(255) | YES | | NULL | |
| block | varchar(255) | YES | | NULL | |
| creator | varchar(255) | YES | | NULL | |
| creationdate | date | YES | | NULL | |
| lastupdator | varchar(255) | YES | | NULL | |
| lastupdate | date | YES | | NULL | |
+---------------+--------------+------+-----+---------+----------------+
(f.k - subscription2user. users_no )


ip_range
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| subscription_no | int(11) | NO | MUL | | |
| ip_start | varchar(20) | NO | | | |
| ip_end | varchar(20) | YES | | NULL | |
| hostname | varchar(100) | YES | | NULL | |
| creator | varchar(255) | YES | | NULL | |
| creationdate | date | YES | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
foreign key (subscription_no) references subscription

subscription2user
+-----------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------+------+-----+---------+-------+
| subscription_no | int(11) | NO | MUL | | |
| users_no | int(11) | NO | MUL | | |
+-----------------+---------+------+-----+---------+-------+
foreign key (subscription_no) references subscription,
foreign key (users_no) references users

subscription
+------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+----------------+
| subscription_no | int(11) | NO | PRI | NULL | auto_increment |
| customer_no | int(11) | NO | MUL | | |
| licensekey | varchar(30) | NO | | | |
| subscriptiontype | varchar(10) | NO | | | |
| begindate | date | NO | | | |
| enddate | date | NO | | | |
| maxusers | int(11) | YES | | NULL | |
| price | double | YES | | NULL | |
| regtype | varchar(10) | NO | | | |
| blockinvalidip | varchar(10) | YES | | NULL | |
| autorenewalmail | varchar(10) | YES | | NULL | |
| name | varchar(100) | NO | | | |
| password | varchar(20) | YES | | NULL | |
| creator | varchar(255) | YES | | NULL | |
| creationdate | date | YES | | NULL | |
| lastupdator | varchar(255) | YES | | NULL | |
| lastupdate | date | YES | | NULL | |
| accesstype | varchar(10) | YES | | NULL | |
+------------------+--------------+------+-----+---------+----------------+

Much Much Thanks!!!

[code green]

want a query that will fetch me username( from users table),
Ip_start and ip_end (from ip_range table) and accesstype (from subscription table) for corresponding user_no

Try this

SELECT username,ip_start,ip_end, accesstype 
FROM users
JOIN subscription2user USING(user_no)
LEFT JOIN ip_range ON subscription2user.subscription_no = ip_range.subscription_no
LEFT JOIN subscription USING(subscription_no)

Can't see a need for subscription2user table

[vsailas]

Try this

SELECT username,ip_start,ip_end, accesstype 
FROM users
JOIN subscription2user USING(user_no)
LEFT JOIN ip_range ON subscription2user.subscription_no = ip_range.subscription_no
LEFT JOIN subscription USING(subscription_no)

Can't see a need for subscription2user table

Tried got an error
<br>ERROR 1052 (23000): Column 'subscription_no' in from clause is ambiguous.
subscription2user table connects Subscription and user table <br>
Sorry no Foreign key at 'users' table.

[code green]

Column 'subscription_no' in from clause is ambiguous

This error means the engine cannot decide from which table 'subscription_no' is being referred.
Prefix it with a table name

`tablename`.`subscription_no` 

subscription2user table connects Subscription and user table

Yes I can see that. But why not use a common ID throughout all the tables.
Ah wait a minute. I think I understand.
There are multiple s`ubscription_no` to `user_no`.
Yes it is a good DB design