468,514 Members | 1,427 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,514 developers. It's quick & easy.

Query Join please help

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!!!
Dec 18 '07 #1
3 1161
code green
1,726 Expert 1GB
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
Expand|Select|Wrap|Line Numbers
  1. SELECT username,ip_start,ip_end, accesstype 
  2. FROM users
  3. JOIN subscription2user USING(user_no)
  4. LEFT JOIN ip_range ON subscription2user.subscription_no = ip_range.subscription_no
  5. LEFT JOIN subscription USING(subscription_no)
Can't see a need for subscription2user table
Dec 18 '07 #2
Try this
Expand|Select|Wrap|Line Numbers
  1. SELECT username,ip_start,ip_end, accesstype 
  2. FROM users
  3. JOIN subscription2user USING(user_no)
  4. LEFT JOIN ip_range ON subscription2user.subscription_no = ip_range.subscription_no
  5. 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.
Dec 19 '07 #3
code green
1,726 Expert 1GB
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
Expand|Select|Wrap|Line Numbers
  1. `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
Dec 19 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Jim | last post: by
4 posts views Thread by bhargav.desai | last post: by
6 posts views Thread by tizmagik | last post: by
3 posts views Thread by Richard Hollenbeck | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.