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 - subscription2us er. 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_n o) references subscription
subscription2us er
+-----------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------+------+-----+---------+-------+
| subscription_no | int(11) | NO | MUL | | |
| users_no | int(11) | NO | MUL | | |
+-----------------+---------+------+-----+---------+-------+
foreign key (subscription_n o) 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 | | | |
| subscriptiontyp e | 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!!!
3 1291 code green 1,726
Recognized Expert Top Contributor
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 subscription2us er table
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 subscription2us er table
Tried got an error
<br>ERROR 1052 (23000): Column 'subscription_n o' in from clause is ambiguous.
subscription2us er table connects Subscription and user table <br>
Sorry no Foreign key at 'users' table.
code green 1,726
Recognized Expert Top Contributor
Column 'subscription_n o' in from clause is ambiguous
This error means the engine cannot decide from which table 'subscription_n o' is being referred.
Prefix it with a table name - `tablename`.`subscription_no`
subscription2us er 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_n o` to `user_no`.
Yes it is a good DB design
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Jim |
last post by:
I need help on a query.
There is a common titles database for several radio stations
Some titles are enabled and some are not enabled for each station.
Example of some tables:
Titles Stations Station_Titles
------ -------- -------------
id id Station_ID
Title StationName Title_ID
|
by: Mike |
last post by:
I have had alot of problems with my query, I have a two tables of
informations and just need a simple Query to get what I want but it is
turning out not to be so simple.
Table 1 Has the following information
TransactionNumber LocationSer RMANumber TimeandDate
1 0000045679 234567 10/10/2004 1:30:45PM
2 0000023456 234567 10/10/2004 2:30:24PM
3 0000045679 234678 ...
|
by: d.p. |
last post by:
Hi all,
I'm using MS Access 2003.
Bare with me on this description....here's the situation: Imagine insurance,
and working out premiums for different insured properties. The rates for
calculating premiums are dependant on the country in which the client is in.
Therefore, we have a Country table, with its list of rates, a client table
and then the property table. Getting this is great, works fine, easy!
Problem is, now I need to work out a...
|
by: bhargav.desai |
last post by:
Hello Gurus,
I need help! I have two table, tblCurrent and tblPrevious. What I
want to join the tables, and create a new table that have matching
records from both the tables, plus this new table also includes
records from tblcurrent that were are not in the tblprevious and also
records from tblprevious that are not in tblcurrent!
How can I accomplish this in one query? or Can I?
|
by: tizmagik |
last post by:
I am having a lot of difficulty generating a CrossTab Query based
report. I have looked online for several tutorials and whatnot but I
have not been able to really find what I'm looking for, nor have I been
able to adapt other people's solutions/tips to fit what I need. If
anyone could please help me with the following it would be really
appreciated, thank you!
I need to generate a Report (say: repCrossTab) that grabs it's data
from the...
| |
by: mlarson |
last post by:
I have a program that worked fine then they needed to be able to also see the empty cells (inmate cells) on a housing unit when they ran the query. So what I had to do was take two tables and combine them into one with a query.
The two tables are..inmtinfo and tblcelldata...
inmtinfo
IN_INNUM - primary key
IN_NAME
IN_RACE
IN_BLDING
IN_SECTION
IN_CELLDRM
IN_BED
|
by: Frawls |
last post by:
Hi
I Am am having problems with a stored Procedure that i wrote.
Basically whats happening is that the Stored procedure Runs fine when i
EXECUTE it in SQL Query analyzer.
But when i debug through the application in Visual Studio .NET 2003 the
application an exception when it executes the query.
|
by: funky |
last post by:
hello,
I've got a big problem ad i'm not able to resolve it. We have a server
running oracle 10g version 10.1.0. We usually use access as front end
and connect database tables for data extraction. We have been using
oracle client 10.1.0.2 with it's odbc for a while without problem. The
problem arose when we decided to reconnect all the tables and save
password. Some query became suddenly very slow. Then I've discovered
that the tables...
|
by: Richard Hollenbeck |
last post by:
I hope this isn't too confusing.
The following query runs pretty fast by itself, but when I want to use it in
a report (pasted below the query), it takes at least fifteen seconds to run!
Then I want to analyze the query in Excel and I have to do some manual
tweaking. I'd like to run this same query as a summary for the whole class
without having to manually do it in Excel, rather in an Access report, but
that gets even more complicated....
|
by: write2ashokkumar |
last post by:
hi...
i have the table like this,
Table Name : sample
Total Records : 500000 (Consider like this)
Sample Records:
id ------------ name
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |